cancel
Showing results for
Search instead for
Did you mean:
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 ID Purchase Doc Item ID Posting Month Forecasted value- year 5500711111 10 2018-04 174415.50 5500711111 10 2018-05 174415.50 5500711111 10 2018-06 174415.50 5500711111 10 2018-07 174415.50 5500711111 10 2018-08 174415.50 5500711111 10 2018-09 174415.50 5500711111 10 2018-10 174415.50 5500711111 10 2018-11 174415.50 5500711111 10 2018-12 174415.50 5500711111 10 2019-01 174415.50 5500711111 20 2018-04 335779.50 5500711111 20 2018-05 335779.50 5500711111 20 2018-06 335779.50 5500711111 20 2018-07 335779.50 5500711111 20 2018-08 335779.50 5500711111 20 2018-09 335779.50 5500711111 20 2018-10 335779.50 5500711111 20 2018-11 335779.50 5500711111 20 2018-12 335779.50 5500711111 20 2019-01 335779.50

2 REPLIES 2
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.
Helper I

@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

#### Happy New Year from Power BI

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

#### Check it Out!

Click here to read more about the December 2020 Updates!

#### 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

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

Top Solution Authors
Top Kudoed Authors