Calculate cumulative forecast for upcoming months based on cumulative actual sum final value

[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

[DataNinja777]

Hi @kkanukurthi ,

Your requirement states that the expected output should be in Power Query, but there are also ways to produce your required output using dax. All you need to do is to create a calculated column in your calendar table in your data model to indicate if the month end of the lastnonblank of your Actual measure is before or after the dates in your calendar table, and if it is before, use the Actual measure, and if it is after use the Forecast measure which is filtered for the after the Actual date.  If you are also interested in this approach, I will go ahead and prepare the required output as an example.  So please let me know.  

Best regards,

[kkanukurthi]

Hi   @DataNinja777 , Thanks for the response. PLease check below what I am looking for. It would be good if we can get output in Power Query or just a try If it is not possible, please provide an example in Dax so that I need to get final graph shown below.

Below are the 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 below screenshots for expected output.

Please check above steps and do needful.

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

Thanks