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

[Anonymous]

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?

[Anonymous]

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 · Accepted 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:

• 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