Forum Discussion

nleuck_101's avatar
nleuck_101
Icon for Continued Contributor rankContinued Contributor
4 years ago

Cumulative Forecast Totals not adding up

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!

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      4 years ago

      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.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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