A measure to calculate forecast for each month

[Anonymous]

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

[v-lili6-msft]

hi, @Anonymous 

You must define the period of for each Purchase Doc ID- Purchase 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 ID- Purchase Doc Item ID in a fact table In advance.

Best Regards,

Lin

[Anonymous]

@v-lili6-msft I am not sure I am following. How do I define period(Posting Month) for each Purchase Doc ID- Purchase 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.