Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hej,
I have forecasts that start in the middle of the Fiscal year. I would love to still compare the forecast to the total years actuals. So let's say the fiscal year is from Jan-Dec, the forecast is from June-Dec. I would love to have one combined value that uses actual data for the months not forecasted and forecast data for the months that have been forecasted in June. Kind of like this:
Month | Forecast | Actuals | Forecast incl. Actuals |
Jan | 3000 | 3000 | |
Feb | 3600 | 3600 | |
Mar | 3800 | 3800 | |
Apr | 4000 | 4000 | |
May | 5000 | 5000 | |
Jun | 5000 | 4000 | 5000 |
Jul | 4500 | 6000 | 4500 |
Aug | 4600 | 4600 | |
Sep | 4800 | 4800 | |
Oct | 4900 | 4900 | |
Nov | 4600 | 4600 | |
Dec | 5000 | 5000 |
So far Ive tried the following but I can't access my data without an aggregation function:
m3 Forecast incl Actuals = If(Base[Forecast Date]<=Base[Date],'Key Measures'[m3 Forecast], 'Key Measures'[m3 Actuals])
Anyone know how to do this?
Thank you!
@TODEI , Try like
m3 Forecast incl Actuals =
var _min = minx(Filter(allselected(Base), Not(isblank([forecast])) ), Base[Date])
return
If(max(Base[Forecast Date])<=_min,'Key Measures'[m3 Forecast], 'Key Measures'[m3 Actuals])
Sadly it is only showing forecast data now and no actuals. I'll try to play around with it a bit and see if I can find a solution.
I did learn that you can use variables! So thank you for that 🙂
User | Count |
---|---|
106 | |
85 | |
81 | |
73 | |
71 |
User | Count |
---|---|
111 | |
102 | |
97 | |
74 | |
67 |