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:




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:


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:


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:


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 =>
    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], 

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


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:

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

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.

60 responses

  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?


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

      • Hi Chris,

        I am a new comer to PBI and DAX but not that bad with databases & formulas…
        I understand the logic here but am stuck with some newbee’s questions.
        Where do you create the function called “CreatePeriodTable” in PBI ?
        Where/how do you create the subsequent Method to generate the table based on the boundary dates ? I am guessing that at some point it should be triggered by the slicer’s item selection but this is not obvious to me.
        If this is, could you please give an advice on where I should land on (basic tutos or whatever would be much appreciated).

        Anyway, thanks a ton.
        This is really really interesting.


  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:

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

  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
    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”,

    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. Pingback: Creating Tables In Power BI/Power Query M Code Using #table() – Chris Webb's BI Blog

  9. Pingback: SQL Server and Power BI Updates – Roundup #47 – Guy in a Cube

  10. 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.


    • 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

  11. 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?


      • 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.

      • I ran into the prior year question too. I think it’s something to do with the bi-directional cross-filtering that must be filtering out prior year dates in some way that breaks your typical PY measures. (It works if you switch off bi-directional cross-filtering, but then the period table stops working.)

        One challenge I ran into with the inactive relationship workaround (and maybe I’m doing something wrong?) is that the prior year amounts are being associated to the prior year dates. So, instead of being able to look at yesterday and see the PY sales side-by-side, there’s a 364 day gap between the number for yesterday and the number for yesterday last year.

        I could add prior year periods as periods so the end-user can flip between this year and last year (which doesn’t solve the side-by-side problem but eliminates the gap). For now I’m using the parameter table technique, which works well but requires each measure to be set up. This technique caught my attention because it would work for all measures out of the box.

      • Great solution! 🙂 But I am not sure how you made the LY / PY input/column so it work just like the solution Chris made. Is there any chance you can describe it a bit more?

  12. Pingback: Create Dynamic Periods for Fiscal or Calendar Dates in Power BI – Gilbert Quevauvilliers – BI blog

  13. 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.

  14. 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?


  15. 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 …

  16. GREAT post !! Many thanks.!!
    Any ideas about which expressions to add new time period to the list such as: Yesterday AND quarter to date ?

    Many thanks.

  17. This has helped my reporting immensely – so thank you.
    However, I have a series of measures that calculate net gain/loss over periods selected by the slicer and am having difficulty calculating the ‘start point’.
    In your sales example, if I were to select current week to date how would I calculate the total sales and units up to – but not including – the start of the current week?

    • No, I don’t think you’ll be able to use this exact same approach, but there’s no reason why you couldn’t recreate the tables that I’ve constructed in M but using SQL.

  18. CalendarAuto to the rescue and Abracadabra! thoughts?

    Period =
    VAR DateTable = CALENDARAUTO(12)
    VAR Today = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],DAY) = 0),”RelativeDate”,”Today”,”Sort”,1)
    VAR Yesterday = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],DAY) = -1),”RelativeDate”,”Yesterday”,”Sort”,2)
    VAR LastWeekday = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(IF(WEEKDAY(TODAY(),2)=1,TODAY()-3,TODAY()-1),[Date],DAY) = 0),”RelativeDate”,”LastWeekday”,”Sort”,3)
    VAR ThisWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = 0),”RelativeDate”,”ThisWeek”,”Sort”,4)
    VAR LastWeek = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date]-1,WEEK) = -1),”RelativeDate”,”LastWeek”,”Sort”,5)
    VAR ThisMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = 0),”RelativeDate”,”ThisMonth”,”Sort”,6)
    VAR LastMonth = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],MONTH) = -1),”RelativeDate”,”LastMonth”,”Sort”,7)
    VAR ThisYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = 0),”RelativeDate”,”ThisYear”,”Sort”,8)
    VAR LastYear = ADDCOLUMNS(FILTER(DateTable,DATEDIFF(TODAY(),[Date],YEAR) = -1),”RelativeDate”,”LastYear”,”Sort”,9)

  19. GREAT post !! Many thanks.!! I truly love this – I’ve utilized it on a few reports now and have included my very own portion date periods to the rundown. I’m new to Power Query and Power BI however and I consider how I would do this for a budgetary year (finishing 30/06), e.g. Current Financial Year to Date. Expressions of remorse if this has just been secured.

  20. Pingback: Fourteenth Blog Birthday « Chris Webb's BI Blog

  21. Hello,

    I know this thread is older but I’m looking to find out the Previous Week Dates using this method. Can you help with that Chris?


  22. Hi everyone,

    I need Period Table, where hourly rows for each date is also mentioned, so that one may not miss hourly trends for One day visualisation.
    Please help/guide me to get M query code for Period table where each date have 24 rows for each hour daily.

  23. Hi Chris!
    I was wondering, why are you adding -1 month and then adding #duration 1 day? For example: Date.AddMonths(TodaysDate,-1) + #duration(1,0,0,0)

    Appreciate your blog <3

  24. Pingback: Power BI Premium Performance – Part 2 | Optimization of your Power BI Model - Reporting/Analytics Made easy with FourMoo and Power BI

  25. HI Chris, can you explain how to create a time period from another column. I have a calendar table that has Min date as earliest date in the range and i’d like to use this to get total to date from the start of the project over a year ago

  26. Chris, Love your solution here. Is there a way to add Last Week, Last Month & Last Year to Date to the current solution?

  27. Figured out how to add a few more things to the solution:

    TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
    Ranges = {
    {“Current Week to Date”,
    {“Previous Week”,
    Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Monday)),-1),
    {“Current Month to Date”,
    {“Previous Month”,
    Date.AddMonths( Date.From(Date.StartOfMonth(TodaysDate)), -1),
    Date.AddMonths( Date.From(Date.EndOfMonth(TodaysDate)), -1),
    {“Current Year To Date”,
    {“Previous Year”,
    Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)), -1),
    Date.AddYears(Date.From(Date.EndOfYear(TodaysDate)), -1),
    GetTables = List.Transform(Ranges,
    each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables)


  28. Pingback: Dimensión período - Power BI SP

Leave a Reply to Chris Webb Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: