topic Rolling 12 in DAX Commands and Tips

Rolling 12

P-Lag — Mon, 13 Mar 2023 06:48:06 GMT

Hi,

I have used two dax measures to calculate a Rolling 12 Months Sales. The first one results in correct values, and the other measure results in a correct "end"; I don't understand why the value differs.
How can I change the measures so that values match "Test 1, Rolling 12 (Revenue)" and ends like "Test 2, Rolling 12 (Revenue)"? The below picture shows charts of the Dax measures. I want it to end with the value for the last date, like "Test 1..." - not like the other, which ends with a value drop.

Thanks in advance.

Test 1, Rolling 12 (Revenue) =

Var MAxDate = MAX (Dates[Date])

Return

Calculate (

[Revenue €],

filter(

All(Dates),

AND (

Dates[Date] <=MaxDate,

DateAdd (

Dates[Date],

YEAR

) > MaxDate

)

And

Test 2, Rolling 12 (Revenue) =

CALCULATE (

[Revenue €],

DATESINPERIOD ( 'Dates'[Date],

MAX ( 'Dates'[Date] ),

-12,

MONTH

)

Re: Rolling 12

MAwwad — Tue, 07 Mar 2023 11:16:36 GMT

It looks like the difference between the two measures is in how they handle the filter context. Test 1 is using the "Calculate" function with a filter applied to the "All(Dates)" table, which means it ignores any other filters that may be applied to the report. Test 2 is using the "Calculate" function with a filter applied directly to the "Dates" table, which means it only considers the dates that are currently visible in the report.

To make Test 2 match the results of Test 1, you can change the filter in Test 2 to use the "All" function, like this:

Test 2, Rolling 12 (Revenue) = CALCULATE ( [Revenue €], DATESINPERIOD ( ALL('Dates'), MAX('Dates'[Date]), -12, MONTH ) )

This will make Test 2 ignore any filters that may be applied to the "Dates" table, just like Test 1 does with the "All(Dates)" table.