I am trying to create a measure for calculating employee attrition forecast for future months based on certain logic covered on below measure.
VAR_FcstAttrMTD in below measure gives the MTD attrition number not exactly but close to the expected numbers. Further to that I am trying to get commulative value of MTD forecasted attrition numbers over the period by using DAX ADDCOLUMNS. I have used an empty table named '0Measures_HC' for ADDCOLUMNS.
I am struggling to get the commulative number and looking for guidance on where I am going wrong.
I also tried to use a custom calendar table for ADDCOLUMNS function and that gives a very high MTD attrition number and no commulative numbers. Request your help.
Attrition Fcst YTD =
VAR _StartPeriod = MIN('6Calendar'[Date])
VAR _EndPeriod = MAX('6Calendar'[Date])
VAR _CurrentDate = TODAY()
VAR _ActualClosing =[0Beginning HC]-[Attrition Actual YTD]
VAR _ExpectedAttrition= (_NextMonthOpening+[Gross Adds Actual MTD])*[Attrition% Fcst MTD]/12
VAR _FcstAttrMTD= IF(_StartPeriod>_CurrentDate,_ExpectedAttrition)
VAR _FcstAttrYTD = ADDCOLUMNS('0Measures_HC',"_AttritionForecast",_ExpectedAttrition,"_AttrDate",_EndPeriod)
VAR _FinalNumber_1 = SUMX(Filter(_FcstAttrYTD, [_AttrDate]=_EndPeriod),[_AttritionForecast])
VAR _FinalNumber = Calculate(IF(_StartPeriod>_CurrentDate,_FinalNumber_1),FILTER(ALL('6Calendar'),_StartPeriod=_StartPeriod))
I created a pbix file with just one table for dates and 2 meaures. Don't see an option to attach the file here.
However, the model is pretty simple. The 1st measure is for monthly value given below. Pls note that I have kept 3 VARs _AddMTDColumn1, 2 and 3 just for trying different options.
VAR _StartPeriod = MIN('Calendar'[Date])
VAR _AddMTDColumn1=ADDCOLUMNS ('Calendar',"_MTD", 5,"_MTDDate",MAX('Calendar'[Date]))
VAR _AddMTDColumn2=ADDCOLUMNS ('Calendar',"_MTD", 5/DurationDay,"_MTDDate",MAX('Calendar'[Date]))
VAR _AddMTDColumn3=ADDCOLUMNS ('Calendar',"_MTD", IF('Calendar'[Date]=MAX('Calendar'[Date]), 5),"_MTDDate",MAX('Calendar'[Date]))
VAR _MTDNumber = SUMX(FILTER(_AddMTDColumn1,[_MTDDate]=MAX('Calendar'[Date])),[_MTD])
I get commulative numbers for 2nd measure if I use VAR_AddMTDColumn1 in the 1st measure. However, the commulation in 2nd measure doesn't work if i use VAR_AddMTDColumn2 or VAR_AddMTDColumn3 in the 1st measure.
What want the first measure to give is a value of 5 for each month. 2nd measure should give commulative value of 1st measure. 5 in fist month, 10 in 2nd month, 15 in the 3rd month and so on.