cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

A measure to calculate forecast for each month

Hi all.

 

I have a table that looks like below. If you notice the 'Forecasted Value- Year' is repeated for every month. That value is actually a value forecasted for the whole year for Purchase Doc ID- Purchase Doc Item ID combination.

 

What I want is a measure that calculates 'Forecasted Value- Year' / 12 (for each month) even for a month which does not have a Posting Month. For example: for 5500711111 - 10 combination there is no Q1 months in the 'Posting Month'. But I still want the Forecasted amount for Jan/ Feb/ March  as 174415.50/12. SO that when I am creating a chart I can see for Q1 the amount Forecasted was X but the actual amount Posted was Y (0 in above example). Make sense?

 

I tried creating a date table, but still doesn't work. Any thoughts?

 

Purchase Doc IDPurchase Doc Item IDPosting MonthForecasted value- year
5500711111102018-04174415.50
5500711111102018-05174415.50
5500711111102018-06174415.50
5500711111102018-07174415.50
5500711111102018-08174415.50
5500711111102018-09174415.50
5500711111102018-10174415.50
5500711111102018-11174415.50
5500711111102018-12174415.50
5500711111102019-01174415.50
5500711111202018-04335779.50
5500711111202018-05335779.50
5500711111202018-06335779.50
5500711111202018-07335779.50
5500711111202018-08335779.50
5500711111202018-09335779.50
5500711111202018-10335779.50
5500711111202018-11335779.50
5500711111202018-12335779.50
5500711111202019-01335779.50

 

 

2 REPLIES 2
Community Support
Community Support

hi, @atulj10 

You must define the period of for each Purchase Doc IDPurchase Doc Item ID in a fact table In advance.

For example: for 5500711111 - 10, there is no 2018-01/2018-02/2018-03 in Posting Month, you want to show them in a chart. but there are also a lot of other year-month in the table, eg. 2017-01/2016-01... why they are not shown in the chart?

So you must define the period of for each Purchase Doc IDPurchase Doc Item ID in a fact table In advance.

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-lili6-msft I am not sure I am following. How do I define period(Posting Month) for each Purchase Doc IDPurchase Doc Item ID if the data does not exist for those months? I want my measure to calculate for each month even if there is no "Posting Month in the fact table.

Just to clarify, the "Forecasted Value-Year" is a value which is forecasted for the whole year. Instead of that value getting repeated across every month in the fact table, I want that value to bedivided by 12 and be shown in every month.

 

I hope that made sense.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors