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:
[sourcecode language=”text” padlinenumbers=”true”]
(
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
[/sourcecode]
For example, calling this function like so:
[sourcecode language=”text”]
CreatePeriodTable("Demo", #date(2016,1,1), #date(2016,1,5),1)
[/sourcecode]
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:
[sourcecode language=”text”]
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
[/sourcecode]
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.
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!
Yeah i was amazed too.
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
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.
Thomas
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.
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
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 ?
Yes, that looks right. I guess it defaults to Monday being the first day of the week because that’s how it is in the US.
Great article. Thanks for this Chris.
Very cool, never used a bi-direcional filter that way. Thanks for the M-Query, next time i will use it.
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.
Bidirectional cross-filtering in relationships doesn’t work in Excel 2013. Instead, you need to replicate the functionality in the DAX for your measures, using code similar to what’s described here: http://blog.gbrueckl.at/2012/05/resolving-many-to-many-relationships-leveraging-dax-cross-table-filtering/
I would add that in Excel 2016 it is more efficient using CROSSFILTER function – you use the same feature used by Power BI when it uses crossfilter. We have to update our articles and whitepapers just for that 🙂 but I will write an article soon explaining that.
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
Hi Chris,
Thanks for your work on this. Wondering if you can help me out with the M code needed to make this work. I’m also looking for the exact same scenario where I have a period called “Current Fiscal Year to Date” and it would include dates from July 1, 2020 to now. Would also like to do previous fiscal year but could probably figure that out once I see the code.
Thanks!
Ryan
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?
I think the best approach would be to use DAX in your PY measure to shift the dates back one year, no need for a parameter table. It should be pretty straightforward – there are some good examples here http://www.daxpatterns.com/time-patterns/
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!
Can you try:
CALCULATE(‘Sales'[Sales]; SAMEPERIODLASTYEAR(‘Date'[CalendarDateKey]), All(‘Date’))
I tried but it still doesn’t work
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.
Andrew: Great idea. It worked perfect, thanks a lot.
Chris: Thank you for your suggestion also.
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?
well, using ALL(Date) I still got blank results. I changed this part to All(‘Period'[Period]) and it worked properly.
Code:
CALCULATE(‘Sales'[Sales], SAMEPERIODLASTYEAR(‘Date'[CalendarDateKey]), , *All(‘Period'[Period])*)
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.
It should be, although I haven’t tested it: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-bidirectional-filtering/
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
This doesn’t work with a direct connection to SSAS Multidimensional, but you could easily recreate the same functionality inside SSAS in a number of different ways.
Thanks Chris, but, Is it possible to get a SSAS multidimensional connection, and show the info for the current year in the Power Bi report?
The filters aren’t dynamic….
You need to build this into your cube somehow. My preferred approach is to build an extra attribute on your Date dimension that does something similar to this: https://blog.crossjoin.co.uk/2013/01/24/building-relative-date-reports-in-powerpivot/
You can then select the “Current Year” member on this attribute in your reports.
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 ?
If you download the sample file (the link is at the end of the post) you’ll see how it’s all implemented 🙂
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 …
Not to worry. Figured it out! Thx! Now will try to modify to add QTD. Thanks
Sorry not to reply sooner – it’s been a busy week. Glad you figured it out!
Great post.
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.
Nevermind, found it 🙂
@Brechet, please can you help me on how you got the Yesterday and QTD to work. struggling with that now. thanks
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?
That’s probably the point where you need to start using DAX to do calculations in measures rather than use this technique. This page is a good starting point: http://www.daxpatterns.com/cumulative-total/
Would it work with Direct Query?
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.
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)
Return
UNION(Today,Yesterday,LastWeekday,ThisWeek,LastWeek,ThisMonth,LastMonth,ThisYear,LastYear)
Looks good to me. I guess it comes down to whether you prefer writing M or DAX.
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.
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?
Thanks,
Colt
HI. I can’t download the example workbook. Is it still available?
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.
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
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
Chris, Love your solution here. Is there a way to add Last Week, Last Month & Last Year to Date to the current solution?
Figured out how to add a few more things to the solution:
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
Ranges = {
{“Today”,
TodaysDate,
TodaysDate,
1},
{“Yesterday”,
Date.AddDays(TodaysDate,-1),
Date.AddDays(TodaysDate,-1),
2},
{“Current Week to Date”,
Date.From(Date.StartOfWeek(TodaysDate,1)),
TodaysDate,
3},
{“Previous Week”,
Date.AddWeeks( Date.From(Date.StartOfWeek(TodaysDate,Day.Monday)),-1),
Date.From(Date.StartOfWeek(TodaysDate,Day.Sunday)),
4},
{“Current Month to Date”,
Date.From(Date.StartOfMonth(TodaysDate)),
TodaysDate,
5},
{“Previous Month”,
Date.AddMonths( Date.From(Date.StartOfMonth(TodaysDate)), -1),
Date.AddMonths( Date.From(Date.EndOfMonth(TodaysDate)), -1),
6},
{“Current Year To Date”,
Date.From(Date.StartOfYear(TodaysDate)),
TodaysDate,
7},
{“Previous Year”,
Date.AddYears(Date.From(Date.StartOfYear(TodaysDate)), -1),
Date.AddYears(Date.From(Date.EndOfYear(TodaysDate)), -1),
8}
},
GetTables = List.Transform(Ranges,
each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables)
in
Output
How do you handle previous month when it is a short month like february, when using the previous month? it only shows 28 days as opposed to 31 that should exist in previous month.
Mino Jalal,
You shouldn’t have any issues with Feb being a short month. The calculation is off Months, NOT Days.
Thanks
Jarrett
Hi Chris. I can’t download the example workbook. Is it still available?
Sorry about that – it got deleted. It’s back now.
Hi Chris! This has been super helpful. My data set is only in weeks and gets updated every month. Is there a way I can code “Todays Date” as the Max date in a separate table so that all of my selections are based on that date instead of Today?
Hello Chris.
I implemented these steps, and it works great. I am using it as a page slicer with multiple visuals etc. No problem at all…
UNTIL I want to select value on a slicer at the same time having Same Period Last Year calculations on this page. They are just blank. For example, when I have Date and [Sales] in the matrix, it shows perfectly correct calculations and dates. But [Sales Same Period Last Year] is always blank. SPLY =
CALCULATE( [Sales], SAMEPERIODLASTYEAR( ‘Date'[Date] ) ).
P.S. I’ve tried various approaches with TREATAS, DATEADD etc. yet this measure always returns blank.
Would greatly appreciate any help 🙂
Thank you!
Hello Chris.
I implemented these steps, and it works great. I am using it as a page slicer with multiple visuals etc. No problem at all…
UNTIL I want to select value on a slicer at the same time having Same Period Last Year calculations on this page. They are just blank. For example, when I have Date and [Sales] in the matrix, it shows perfectly correct calculations and dates. But [Sales Same Period Last Year] is always blank. SPLY =
CALCULATE( [Sales], SAMEPERIODLASTYEAR( ‘Date'[Date] ) ).
P.S. I’ve tried various approaches with TREATAS, DATEADD etc. yet this measure always returns blank.
Would greatly appreciate any help 🙂
Thank you!
Perfect! One more great post, congratulations!
How I can use this in a Matrix if I have to show data for multiple periods?