Cumulative Forecast Totals not adding up

[nleuck_101]

Hello All,

I'm having some trouble getting my Cumulative total to work correctly. I tried using the Enterprise DNA Youtube video for Create Automatic Forecasts From Historic Data in Power BI using DAX but it's not working as expected.

https://www.youtube.com/watch?v=vq3VOERJw7s

Measure to calculate Total that is used in the Forecast:

Total = SUMX(Table, Table[Column])

Here is my Forecast measure:

measure =

VAR TotalLM = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1))

VAR Total2MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-2, 1))

VAR Total3MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-3, 1))

VAR Total4MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-4, 1))

VAR Total5MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-5, 1))

VAR Total6MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-6, 1))

VAR Total7MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-7, 1))

VAR Total8MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-8, 1))

VAR Total9MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-9, 1))

VAR Total10MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-10, 1))

VAR Total11MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-11, 1))

VAR Total12MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-12, 1))

VAR Total13MthsAgo = CALCULATE([Total], DateTable[date_field] = DATE(YEAR(TODAY()), MONTH(TODAY())-13, 1))

VAR Factor = .0038

RETURN

DIVIDE(

TotalLM + Total2MthsAgo + Total3MthsAgo + Total4MthsAgo + Total5MthsAgo + Total6MthsAgo + Total7MthsAgo + Total8MthsAgo + Total9MthsAgo + Total10MthsAgo + Total11MthsAgo + Total12MthsAgo + Total13MthsAgo, 13, 0) * Factor

And then here is my Cumulative Total Forecast measure:
Cumulative Total Forecast = CALCULATE([Forecast], FILTER(ALLSELECTED(DateTable), DateTable[date_field] <= MAX(DateTable[date_field])))

But the results only show what the increase should be which is an increase of 17.

What I should expect is Apr-2022 be 4578 and then May-2022 be 4595 and then June-2022 be 4612 and so on.

Any help would be greatly appreciated!

[v-janeyg-msft]

Hi, @nleuck_101 

Without data and context it's hard for me to determine where you're going wrong. You can check step by step which result is wrong by splitting the measure. If you can share some sample data and your calculation logic and your desired result, we can help you more accurately.

Best Regards,
Community Support Team _ Janey

[amitchandak]

@nleuck_101 , if you need 13 month avg of sum measure you can try a measure like

Rolling 13 = calculate(AverageX(Values('Date'[MONTH Year]),[Total] ),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-13,MONTH))

[nleuck_101]

@amitchandak 

Unfortunately, that will not work for me. I need the Cumulative sum. I need the 17 to be added to Apr. Then May will be that total plus another 17 and so on.