Can I please kindly ask for your help and support with the following issue (the pbix file is attached)?
The pbix file is just a simplified version. I have to calculate Annual Headcount Average over the reporting year that consists of actual months (YTD) and forecast months (YTG). I have two measures - one for average actual and another for average forecast. Now I am struggling to combine the two measures to be able to calculate Annual Average.
Please note that Full Year Forecast is dynamic based on stakeholders’ request, i.e. a user can choose forecast version and Full Year Forecast will be pulled, e.g. 5+7 forecast means 5 months of actual and 7 months of the forecast that was loaded at the end of May.
pbix file shows that we have a version of forecast loaded every month into ERP database.
I see that the data model does not connect to the Fct_Jrnl table. Perhaps that was an oversight? When I connect it, there doesn’t seem to be any Journal Types that match.
In my real model I have a dimensional (lookup) table with various journal types.
I did not replicate the Journal Type to the sample model as it is irrelevant. I am using the Forecast Journal table as a slicer to “grab” the forecast version selected.
I would like to let you know - just in case you will come with the same issue/task - the only way I could get the calculation in DAX for AVERAGEX for a mix of actual and forecast data was through Power Query. I marked rows for each forecast version, ie. 2+10 would have all actual from Ledger A for period 1 and 2 and 10 months from Ledger F for period 3 to 12, etc. Via list functions. Then from the row context it was easy to calculate AVERAGE of mixed data.
Try using below measure and check if you get desired result. If seems lengthy or complex, then you can create FCT_YTG and ACT_YTD as separate measure and use inside this Final measure.
Forecast Average Headcount by Month =
VAR MonthInUse = SELECTEDVALUE(Fct_Jrnl[Month])
VAR DateInUse = DATE(2021, MonthInUse,1)
VAR FctDateMarker = DATE(2021, MonthInUse+1,1)
VAR FCT_YTG = CALCULATE(
[Forecast Monthly Average Headcount],
_db_table[AccPer_#date] > DateInUse,
_db_table[Transaction Date]=FctDateMarker
)
VAR ACT_YTD = CALCULATE(
[Actual Monthly Average Headcount],
_db_table[AccPer_#date]<= DateInUse
)
RETURN if(HASONEVALUE(Dates[YYYY-MMM]),FCT_YTG + ACT_YTD,CALCULATE(AVERAGEX(SUMMARIZE(_db_table,_db_table[Ledger],_db_table[AccPer_#date],"@val",if(_db_table[Ledger] = "K",CALCULATE(
[Actual Monthly Average Headcount],
_db_table[AccPer_#date]<= DateInUse
),CALCULATE(
[Forecast Monthly Average Headcount],
_db_table[AccPer_#date] > DateInUse,
_db_table[Transaction Date]=FctDateMarker
))),[@val]),ALLSELECTED(_db_table[Ledger]))
)
