Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I recently learned about a way of using custom time periods eg last 4wks, last QTR, YTD etc in slicers (to avoid using bookmarks).
This works by creating a table with Dax, made up of appended tables using Union with associated dates.
Dim_Time_Periods = VAR MaxDate = MAX( Dim_Calendar[calendar_date] ) RETURN UNION( ADDCOLUMNS( DATESBETWEEN( Dim_Calendar[Date], MaxDate - ( 4 * 7 ) + 1, MaxDate ), "Type" , "4wks" ), ADDCOLUMNS( DATESBETWEEN( Dim_Calendar[Date], MaxDate - ( 13 * 7 ) + 1, MaxDate ), "Type" , "13wks" ) )
I then create a bi-directional relationship between this table and my date table, Dim_Calendar, and add a slicer with "Type" into the report page.
This works fine for the specific time period I select, however it has broken all my growth / year ago measures. I have tried DATEADD and SAMEPERIODLASTYEAR (using both Dim_Calendar[Dates] and Dim_Time_Periods[Dates]) but both are resulting in blank measures.
Does anyone know a way of making this work?
Solved! Go to Solution.
Hi @Anonymous
I would recommend fixing this by adding ALLCROSSFILTERED ( Dim_Calendar ) as a modifier to any time intelligence expressions using CALCULATE.
For example:
Dollars Last Year =
CALCULATE (
[Dollars],
SAMEPERIODLASTYEAR ( Dim_Calendar[Date] ),
ALLCROSSFILTERED ( Dim_Calendar )
)
The reason this is needed is:
Does this work for you?
Regards
Hi Owen,
Thanks so much for taking the time to write out a detailed response, including an explanation on how & why it works. This worked exactly as I hoped - much appreciated!
Hi @Anonymous
I would recommend fixing this by adding ALLCROSSFILTERED ( Dim_Calendar ) as a modifier to any time intelligence expressions using CALCULATE.
For example:
Dollars Last Year =
CALCULATE (
[Dollars],
SAMEPERIODLASTYEAR ( Dim_Calendar[Date] ),
ALLCROSSFILTERED ( Dim_Calendar )
)
The reason this is needed is:
Does this work for you?
Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
25 | |
16 | |
14 | |
13 |