Rolling 12 month total

[maurcoll]

Hi

I am trying to calculate the last rolling 12 months data that i can put into a line chart visual.

It will need to show monthly going back for 12 months as per the below data. So for Sept 23 it will show the sum of data for the October 22 - Sept 23. August 23 will show data for Sept 22 - Aug 23 and so on.

Total	MonthYear	Rolling 12 months
40	01/01/2022
78	01/02/2022
136	01/03/2022
275	01/04/2022
360	01/05/2022
463	01/06/2022
522	01/07/2022
612	01/08/2022
687	01/09/2022
761	01/10/2022
838	01/11/2022
887	01/12/2022	5659
906	01/01/2023	6525
951	01/02/2023	7398
974	01/03/2023	8236
908	01/04/2023	8869
880	01/05/2023	9389
876	01/06/2023	9802
890	01/07/2023	10170
887	01/08/2023	10445
881	01/09/2023	10639

This is the calculation i am using but it is showing monthly totals on the chart not the cumulated 12 month total.

Rolling 12 Months =

CALCULATE(

    SUM( Total[Total]),

    DATESINPERIOD( Date[MonthYear], MAX( Date[MonthYear] ), -12,  MONTH )

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[ThxAlot]

[jgeddes]

A measure like the following might work for you...

rollingTotalMeasure =
var _rollingMonths =
12
var _maxDate =
MAXX(
    ALL('Table'),
    [MonthYear]
)
Return
CALCULATE(
    SUM('Table'[Total]),
    FILTER(
        ALL('Table'), 
        [MonthYear] <= MAX('Table'[MonthYear]) && DATEDIFF([MonthYear], _maxDate, MONTH) < _rollingMonths
    )
)