Rolling 12 Month Calculation (Not Cumalative)

[dinhandy1]

Hi,

I need help with creating a DAX measure that shows roling 12 month values.

I tried creating a measure as below but it shows cumalative and is wrong..

Rolling 12 CA =

CALCULATE (

[Total CA $],

DATESBETWEEN (

'Rolling 12'[reportdate],

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Rolling 12'[reportdate] ) ) ),

LASTDATE ( 'Rolling 12'[reportdate] )

)

)

[tamerj1 · Accepted solution]

Hi @dinhandy1 

you can try

Rolling 12 CA =
VAR CurrentDate =
    MAX ( 'Rolling 12'[reportdate] )
VAR StartDate = CurrentDate - 364
RETURN
    CALCULATE (
        [Total CA $],
        REMOVEFILTERS ( 'Rolling 12' ),
        'Rolling 12'[reportdate] >= StartDate,
        'Rolling 12'[reportdate] <= CurrentDate
    )

If you have filter safe columns (columns that you use in slicers) then you need to replace REMOVEFILTERS with ALLEXCEPT and add the filter safe columns inside it. 

[dinhandy1]

Thank you @tamerj1 ! this works