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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jaqcues
Frequent Visitor

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 @jaqcues 

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
jaqcues
Frequent Visitor

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 @jaqcues 

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors