12 Month Running Total as calculated column, not measure

[Anonymous]

I'd like to create a calculated column that shows running total. I've found plenty of examples for measures, but not columns.

For example, here is the measure. Can you help me convert this to a column?

SalesRolling =
CALCULATE (
    SUM ( 'Claims'[CLM_ACTL_PD_AMT] ),
    DATESBETWEEN (
        'Calendar'[FullDate],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[FullDate] ) ) ),
        LASTDATE ( 'Calendar'[FullDate] )
    )
)

[rajulshah]

Hello @Anonymous ,

If you want runnin total for the current year , you can use the DAX function TOTALYTD.

You can refer here:

https://msdn.microsoft.com/en-us/library/ee634400.aspx

Hope this helps.

Let me know if you need further help.

[Anonymous]

Nope, I want rolling 12 months, not YTD. Thanks though!

Also, my code isn't working because it's showing the running totals for all members in the table, even when I'm only showing one member. Back to the drawing board.

[Anonymous · Accepted solution]

This seems to be working:

SalesRolling12M =
CALCULATE (
    SUM ( 'Claims'[CLM_ACTL_PD_AMT] ),
    ALLEXCEPT ( 'Claims', 'Claims'[MBR_SK] ),
    DATESBETWEEN (
        'Calendar'[FullDate],
        DATEADD ( LASTDATE ( 'Calendar'[FullDate] ), -11, MONTH ),
        //Eleven Months Ago
        LASTDATE ( 'Calendar'[FullDate] )
    )
)

This helped me: https://stackoverflow.com/questions/23835611/how-to-sum-a-column-based-on-another-column-in-dax-powe...

[Anonymous]

This seems to work. Let me know if there's a more elegant solution:

SalesRolling12M = 
CALCULATE (
    SUM ( 'Claims'[CLM_ACTL_PD_AMT] ),
    ALL ( 'Claims' ),
    DATESBETWEEN (
        'Calendar'[FullDate],
        DATEADD ( LASTDATE ( 'Calendar'[FullDate] ), -11, MONTH ), //Eleven Months Ago
        LASTDATE ( 'Calendar'[FullDate] )
    )
)