Cummulative TTM Total

Question

I need help calculating a Trailing Twelve Month total for each month. I want to ensure each selected month reflexes that month total and 11 prior months i.e. August 2019 will display total sales from Sept 2018 to August 2019, Jan sales will display Feb 2018 to Jan 2019.

Attached is a Data set with the date and sales amount columns.

I've created the following DAX which gives me monthly sales but I also need to generate a measure that will return each month total as (sum of 12 months, i.e current month + 11 prior months)

Monthly Sales =
VAR CurrentDate =
    MAX ( 'EOM Date'[EOM Date] )
VAR PreviousDate =
    DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ) - 12, DAY ( CurrentDate ) )
VAR Result =
    CALCULATE (
        [Net Sales],
        FILTER ( 'Table', [Date] >= PreviousDate && [Date] <= CurrentDate )
    )
RETURN
    Result.

JBfreefolks JBfreefolks · Accepted Answer · 2019-10-12T08:11:26

It is rather hard to understand how is your data model regarding your formula, but at least with this you should be able to compute a moving annual total :

Cumulated 12 month Sales :=
CALCULATE (      
    [Net Sales],
    DATESINPERIOD (
        'Table'[Date],,
        MAX ( 'Table'[Date] ),         
        -1,
        YEAR
    )
)

Of course you need to have a Table (which is a date table) link to your sales table

Cummulative TTM Total

1 Answers