Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkanukurthi
Helper I
Helper I

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

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.

 

kkanukurthi_3-1718294288398.png

 

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

 

1 ACCEPTED SOLUTION

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

View solution in original post

4 REPLIES 4
kkanukurthi
Helper I
Helper I

Hi @v-junyant-msft ,

 

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.

kkanukurthi_0-1718342019900.png

kkanukurthi_1-1718342079531.png

 

kkanukurthi_2-1718342113934.png

Please check above steps and do needful.

 

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

kkanukurthi_3-1718342150772.png

 

Thanks

 

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

v-junyant-msft
Community Support
Community Support

Hi @kkanukurthi ,

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

vjunyantmsft_0-1718332227837.png

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

Hi @v-junyant-msft , 

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.

kkanukurthi_0-1718340868727.png

kkanukurthi_1-1718340952058.png

 

kkanukurthi_3-1718341012748.png

kkanukurthi_4-1718341059098.png

Thanks

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors