Forum Discussion

EJ125's avatar
EJ125
Regular Visitor
3 years ago

Creating a Forecast using historical percentage spread

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

 

1 Reply