Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Question about Year Ago measure calcluations not working with custom time periods

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?

 

Capture.PNG

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:

  • Dim_Time_Periods is on the many-side of a bidirectional relationship with Dim_Calendar
  • When a filter is applied within CALCULATE to the nominated Date column of a table marked as a date table (in this case Dim_Calendar), a hidden REMOVEFILTERS ( Dim_Calendar) is added to automatically remove any filters on the expanded Dim_Calendar.
  • However, the expanded Dim_Calendar table only includes itself and any tables on the 1-side of a relationship with it.
  • So filters on the Dim_Time_Periods table are not automatically removed, and must be removed with  a modifier such as ALLCROSSFILTERED ( Dim_Calendar ).
  • You could also use REMOVEFILTERS ( Dim_Time_Periods ) but I prefer the ALLCROSSFILTERED method, just in case the model contains multiple tables on the many-side of relationships with Dim_Calendar.

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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!

OwenAuger
Super User
Super User

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:

  • Dim_Time_Periods is on the many-side of a bidirectional relationship with Dim_Calendar
  • When a filter is applied within CALCULATE to the nominated Date column of a table marked as a date table (in this case Dim_Calendar), a hidden REMOVEFILTERS ( Dim_Calendar) is added to automatically remove any filters on the expanded Dim_Calendar.
  • However, the expanded Dim_Calendar table only includes itself and any tables on the 1-side of a relationship with it.
  • So filters on the Dim_Time_Periods table are not automatically removed, and must be removed with  a modifier such as ALLCROSSFILTERED ( Dim_Calendar ).
  • You could also use REMOVEFILTERS ( Dim_Time_Periods ) but I prefer the ALLCROSSFILTERED method, just in case the model contains multiple tables on the many-side of relationships with Dim_Calendar.

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.