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
