Cumulative Forecast based on Cumulative Actuals last month value (Power query)

[kkanukurthi]

Hello All,

I have Actuals and Forecast in two tables. I have appended month wise values as shown in below screen shot.

Appended table has 4 rows Actuals,Forecast, cumulative actuals and cumulative forecast with the months from March 2024-Feb 2025 and go on.

I would like to calculate cumulative forecast taking first value of last existing cumulative actual value(as a first value) whichever it is dynamically. For example, if we are in June month, the total cumulative actual value is 70(should bring cumulative actual value down). The cumulative Forecast need to pick this June Total value and continue Roliing up totals of monthly Forecast values as shown in below screen shot.

Expected output should be in Power query.

The final graph, should be inclined graph cumulative Actuals vs Cumulative Forecast.

Note : Cumulative Forecast should not start with Forecast cell value.

Thanks in Advance

[kkanukurthi]

Hi @Anonymous ,

Thanks for the response. PLease check below screen shots for calculation process done in excel. There are two tables Actuals and Forecast. After calculation process, i have appended both tables in single table. I understand M function in Power Query only supports column-wise calculations, but i am just checking whether we can acheive the expected output using cumulative actual total value taking as a base and calculate cumulative forecast going months.

All that im looking month-year wise cumulative actuals burnt till current month and Forecast upcoming months using Forecast values..i.e cumulative actuals vs Cumulative forecast. Hard to describe in words please check above screenshot for expected output.

Please check above steps and do needful.

Final Expected output in Excel. Same output expecting in Power Bi viasual

Thanks

[ronrsnfld · Accepted solution]

Could you please explain why you decided to ignore my response to your previous identical question?

[Anonymous]

Hi @kkanukurthi ,

I don't understand how you calculate the Forecast value, can you give an example of the calculation process?

In addition, the M function in Power Query only supports column-wise calculations, not row-wise calculations. If you need to achieve your expected results, you either have to transpose the entire table first or create a lot of custom columns.

Best Regards,
Dino Tao

[kkanukurthi]

Hi @Anonymous , 

PLease check below screen shots for calculation process done in excel. There are two tables Actuals and Forecast. After calculation process, i have appended both tables in single table.

I understand M function in Power Query only supports column-wise calculations, but i am just checking whether we can acheive the expected output using cumulative actual total value taking as a base and calculate cumulative forecast going months.

All that im looking month-year wise cumulative actuals burnt till current month and Forecast upcoming months using Forecast values..i.e cumulative actuals vs Cumulative forecast. Hard to describe in words please check above screenshot for expected output.

Thanks