Creating a Forecast using historical percentage spread

[EJ125]

I'm trying to merge Current Year Actuals with what we are calling as a Total Year forecast number. I need to be able to take the Total Year Number we are calling - Actuals and use the historical spread of actuals to spread the forecast value out for the remainder of the year.

https://www.dropbox.com/s/4bc1de317sljk14/sample_data.pbix?dl=0 

The below gets me historically what each month represents as a percentage of the year 

hist_monthly_sales_percentage =  DIVIDE([Actual], CALCULATE([Actual], ALL(Date_tbl[Fiscal_Mo])))

This gets me current year sales

CY_Sales = calculate(Sum(sales_tbl[Amount]),Date_Tbl[Fiscal_Year]=Year(Today())

I then have a total fcst table that just gives the year total forecast not split out by month

Fcst = calculate(sum(Fcst_Tbl[Amount]))

The difference between [Fcst]-[CY_Sales] is what i need to spread across the remaining months of the year using the percentages calculated above. 

Any information would be greatly appreciated! 

Thanks

[v-shex-msft]

Hi @EJ125,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng