神刀安全网

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi…

One very common requirement when creating a Power BI report is the ability to apply a filter for the current day, week, month, quarter or year. There are several ways of implementing this: you could add relative date columns to your Date table as I showhere (I used DAX calculated columns but you could also do this in M quite easily too ); you could also build the filter into the DAX for your measures, although that could mean you end up with a lot of quite complex measures.

Last week I received an email asking for help with an interesting variation on this problem: how can you create a report with a single slicer that allows you to switch between showing data for the current day, week, month or year? The requirement to have a single slicer is important here: if you create new columns on the date table, that would allow you to have a single slicer that allows for selecting the current day or any relative day, or the current week or any relative week, or the current month and any relative month, or the current year and any relative year,  but it wouldn’t allow you to select weeks, months and years together in the same slicer.

Here are some screenshots showing what we want to achieve: a report with two measures and a single slicer that allows the user to switch between displaying data for a variety of relative time periods:

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

The way to achieve this is not all that different from the calculated column approach, but it requires a separate table to model the many-to-many relationship between all the required relative period selections and the dates in them, as well as the use of bidirectional cross-filtering between tables (which I blogged abouthere). The data model I used for this report looks like this:

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

The Sales table just contains sales data; the Date table is a normal Power BI date table. The Period table is the interesting table here: it contains one row for each combination of relative time period (eg “Today”, “Current Week To Date”, “Rolling Month”) and date:

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

It’s the Period column on this table that is used to create the slicer in the screenshots above. The Sort column is used along with Power BI’s Sort By Column functionality to make the values in the Period column appear in a sensible order in the report.

Notice also that on the relationship between the Period table and the Date table the Cross filter direction property is set to Both:

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

This means that a selection on the Period table travels up the relationship to the Date table and then back down the relationship from Date to Sales. For example, selecting “Current Week” in the Period table will select the dates in the current week on the Date table, which in turn selects the rows for those dates on the Sales table.

The challenge, though, is to write the query to populate the Period dimension. I’ve done this in two parts. First, here’s a function called CreatePeriodTable that returns a table for a single time period selection. It takes the name of the time period and a start date and end date, and will return a table with one row for each date in the date range:

( PeriodName as text,  StartDate as date,  EndDate as date,  SortOrder as number ) as table => let     DayCount = Duration.Days(EndDate-StartDate)+1,     DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),     AddPeriodName = List.Transform(DateList,          each {PeriodName,_,SortOrder}),     CreateTable = #table(         type table[Period=text, Date=date, Sort=number],          AddPeriodName) in     CreateTable

For example, calling this function like so:

CreatePeriodTable("Demo", #date(2016,1,1), #date(2016,1,5),1)

Returns the following table (with the dates shown in dd/mm/yyyy format):

Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi...

Second, here’s a query that calls this function once for each of the time periods you want to be able to select by and creates a single table that contains all of the rows returned by each of the function calls:

let     TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),     Ranges = {                 {"Today",                  TodaysDate,                  TodaysDate,                  1},                 {"Current Week To Date",                  Date.From(Date.StartOfWeek(TodaysDate)),                  TodaysDate,                  2},                 {"Current Month To Date",                  Date.From(Date.StartOfMonth(TodaysDate)),                  TodaysDate,                  3},                 {"Current Year To Date",                  Date.From(Date.StartOfYear(TodaysDate)),                  TodaysDate,                  4},                 {"Rolling Week",                  Date.AddWeeks(TodaysDate,-1) + #duration(1,0,0,0),                  TodaysDate,                  5},                 {"Rolling Month",                  Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0),                  TodaysDate,                  6},                 {"Rolling Year",                  Date.AddYears(TodaysDate,-1) + #duration(1,0,0,0),                  TodaysDate,                  7}              },     GetTables = List.Transform(Ranges,              each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),     Output = Table.Combine(GetTables)      in     Output

In this query the Ranges step contains a list of lists, where each list in the list represents a time period with its start and end dates in the same order that you’d pass these values as parameters to the CreatePeriodTable() function. I’ve deliberately structured the code to make it easy to add new time period to the list. Hopefully the example time periods in the query above give you a good idea of what’s possible in M and all the functions it gives you for calculating different dates. The GetTables step loops over this list and calls the CreatePeriodTable() function for each list in the list, and the Output step combines all the data into a single table.

All of the dates ranges here end with today’s date, as returned by the DateTimeZone.FixedUtcNow() function, but you may want to check out Ken’s post here on handling time zones in M depending on your exact requirements. Because this happens in M when the data is loaded the value of today’s date will be fixed at the point in time that data refresh took place.

You can download the example workbook for this post here .

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Fi…

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址