Use Date measures to select forecast or actuals.

[TODEI]

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!

[amitchandak]

@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])

[TODEI]

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 🙂