Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |