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

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 show here (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:

image

image

image

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 about here). The data model I used for this report looks like this:

image

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:

image

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:

image

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):

image

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.

36 thoughts on “Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering And M

  1. Oh my God! M is really powerful… The code is extremely well structured…. I wish I could write such a beautiful logic/code one day 🙂
    Thanks for this excellent post Chris!

  2. Hi Chris,

    This is a great post on a challenging topic.

    I have a question about the case where a user selects more than one value in the period selection (e.g. Week to date and month to date).

    Will the sales numbers for the current week be doubled (for the dates that are in both selected periods)?

    If it isn’t doubled for these dates, is that a feature of the bi-directional join or the model structure? If It is doubled, is there a way to prevent it?

    Cheers,
    Jon

    • Good question. If the user selects multiple periods then they will see all of the dates for all of the selected periods. If one period overlaps all others then I suppose that would be ok, but in many cases it will give misleading results (maybe doubled, maybe inflated in some other way) so you should make sure the Single Select option is set to On for the Period slicer. You could also use the HasOneValue() DAX function in your measures to make sure only one Period is selected: https://msdn.microsoft.com/en-us/library/gg492190.aspx

  3. In reality every date will be considered only once in the DAX query, so the values will not double because the same date is present in multiple periods – but the problem exists if you make an inconsistent choice, such as current week + current month, or current month + last year month –> in this case, you would consider the set including all the days that appears at least once in each period. The HasOneValue protection suggested by Chris is the best practice in this case.

  4. Hi Chris
    I love your M code to create your Date table:

    let
    DateList = List.Dates(#date(2015,1,1), 730, #duration(1,0,0,0)),
    AddColumns = List.Transform(
    DateList,
    each
    {_, Date.Month(_), Date.Year(_)}
    ),
    DateTable = #table(type table[Date=date, Month=number, Year=number], AddColumns)
    in
    DateTable

  5. Interestingly just opened the .pbix file today Tuesday 31/05/16 and noticed the ‘Current Week to Date” sales figures started as of yesterday Monday 30/05/16.

    If you wish to start your week on another day, say a Sunday then add the optional FirstDay argument to this function.
    For Sunday set it to 0 see https://msdn.microsoft.com/en-us/library/mt260707.aspx
    Chris does the M function Date.StartOfWeek default the FirstDay argument to value = 1 ?ie Monday ?

    So to start the week on a Sunday the code in the ‘Period’ query within the Ranges list of lists should read
    {“Current Week To Date”,
    Date.From(Date.StartOfWeek(TodaysDate,0)),
    TodaysDate,
    2},

    Can you confirm that Is right ?

  6. Very cool, never used a bi-direcional filter that way. Thanks for the M-Query, next time i will use it.

  7. Is there any way to replicate this functionality in Excel 2013 Power Query/Powerpivot? I notice there’s not the Both directions filtering option…. If I just have my calendar table lookup in the period table and use that as a slicer on a third table that has a relationship to the calendar table, I get the “Create a relationship?” dialog and Excel doesn’t know what to do.

  8. Chris,

    I really love this – I’ve used it on a couple of reports now and have added some of my own date periods to the list. I’m new to Power Query and Power BI though and I wonder how I would do this for financial year (ending 30/06), e.g. Current Financial Year to Date. Apologies if this has already been covered.

    Thanks,
    Shona

    • Sorry for the late reply – unfortunately I can’t put together any sample code right now (I will be able to next week if you need it) but the logic will be something like this:
      1) If the current date is greater than June 30th, return the current year, else return the previous year
      2) Create a date for June 30th in the year found in step (1)
      3) Use this date in the M code shown in the post to find the range of dates from the date in step (2) to today’s date

  9. This is awesome. I changed the queries a bit for me so as to just provide MTD, QTD, and YTD. Thank you so much!
    My only question is, how would I pass the selection from this slicer into a measure that would pull the Prior Year date range/values as well. I don’t want to show PY MTD, QTD, or YTD in the slicer, I want to show the PY measures side by side with the current year measures in the report so that I can show the end user year over year variances. My only idea was to use the parameter table technique described by Collie and the Italians. But is there a simpler/better technique?

      • Chris,

        this approach that you are suggesting seems not to be working for me. I’m using your “Month to Date” option from slicer and have to measures, “Sales” and “Sales PY”. For the “Sales” it shows correct number but for the “Sales PY” it’s just blank.

        For the “Sales PY” I use this DAX: CALCULATE(‘Sales'[Sales]; SAMEPERIODLASTYEAR(‘Date'[CalendarDateKey]))

        Any thoughts? Should I use some different approach?

        Thanks!

      • Bruno: I was able to get this to work. What I had to do was use Chris’ method to create a second column in the Period table that shifted the year back one, thus creating a Prior Year column (I handled the leap year issue by making 2/29/2016 relate to 2/28/2015). From here, I created two relationships, both bidirectional, between the Period table and the Calendar table. The active one between the original date column from the period table and the date key in the calendar table. The inactive one between my newly created prior year column of the period table and the date key in the calendar table. Downstream, the calendar table has one relationship This makes the DAX very easy. The Current Year measure is SUM([Sales]). The Prior Year measure is CALCULATE(SUM([Sales]),USERELATIONSHIP(‘PERIOD'[Prior Year Date],’Calendar'[DateKey])). That’s it.

  10. Is it possible to do this using direct query mode? I’ve read that bidirectonal cross filtering isn’t currently supported. Without this, it looks like it will be hard to add any form of relative calendar.

  11. Very interesting post, but, What’s happend if we’ve a direct connection to a SSAS Multidimensional?
    In that case we cannot use “dynamic filters”…

    Is it that possible?

    Thanks

  12. Chris – you are throwing us newbies into the deep end! Any directions on how to setup the two functions ? How is this actually implemented on PowerBI desktop ?

      • Chris: I did look at the sample file. Its not apparent how / where the CreatePeriodTable function is implemented. Also how is the date range in the Period determined? Is it hard coded or is it determined based on the range of dates in your FactTable ? You can see my confusion …

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s