Forum Discussion

P-Lag's avatar
P-Lag
Icon for Helper I rankHelper I
3 years ago

Rolling 12

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],
                1,
                YEAR
            ) > MaxDate
        )
    )
)
And 
 
Test 2, Rolling  12 (Revenue) =
CALCULATE (
      [Revenue €],
      DATESINPERIOD ( 'Dates'[Date],          
                      MAX ( 'Dates'[Date] ),  
                      -12,                   
                      MONTH                
      )
)
 

 



 

1 Reply

  • MAwwad's avatar
    MAwwad
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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.