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 )

[jgeddes · Accepted solution]

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
    )
)

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[ashish_18]

Hi @Ashish_Mathur   Ia m trying to achieve rolling 12 month total like Runways by forecast..This logic is not helping me in powerbi. The highlighted column is achived using excel spreadsheet and row numbers.I want to achive the same using DAX in powerbi.

Runway by CF for Nov = Total of current forecast - current forecast of Nov

Runway by CF for Dec and so on= Runway by CF for Nov- current forecast of Dec   and so on.

Can you please advise if you can gte breakthrough for this logic. 

[Ashish_Mathur]

Hi,

I cannot understand anything there at all.  Share an MS Excel file with your Excel formulas.  I will convert those Excel formulas into DAX measures.

[ashish_18]

Hi Ashish, unable to add excel here but can paste values frome excel as below.

for Nov , =SUM(C2:C13)-C2

for dec  =SUM(C3:C13)-C3

for Jan, =SUM(C4:C14)-C4

and so on

Appreciate your help here,,

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ashish_18]

hi @Ashish_Mathur   thanks for ur help...However I could not read ur pbix due to latest verison of pbi desktop..Anyway I have sorted out that part and my runway by CF is working... 

Can you please help me to convert another excel formulas in DAX for runway calculation

Actual requiremnt is as below.

Runway by Burnrate for Nov = Total of current cost - Nov cost i.e.( C2:C13)-C2
Runway by Burnrate for Dec =Runway by Burnrate for Nov - burn rate of Nov i.e F2-E2
Runway by Burnrate for Jan =Runway by Burnrate for Dec - burn rate of Dec i.e F3-E3
and so on

(C:C ) means All rows of column "current cost". I taken that to take total of that column and minus current cost of Nov (C2)

Actual excel for "AAA" is as below but in main source file AAA,BBB,CCC are added as column "FI name" so runway and burn rate should work when we select AAA,BBB,CCC from slicer.

[Ashish_Mathur]

Hi,

I cannot and will not work with just an image.  Share the download link of the MS Excel file with your formulas already written there.

[ThxAlot]

[jgeddes · Accepted solution]

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
    )
)