I am trying to create a measure for calculating employee attrition forecast for future months based on certain logic covered on below measure.
Attrition Fcst YTD =
VAR _StartPeriod = MIN('6Calendar'[Date])
VAR _EndPeriod = MAX('6Calendar'[Date])
VAR _CurrentDate = TODAY()
VAR _ActualClosing =[0Beginning HC]-[Attrition Actual YTD]
VAR _NextMonthOpening=CALCULATE(_ActualClosing,'6Calendar'[Date]<=EOMONTH(_CurrentDate,-1))
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))
return _FinalNumber
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.
aMTDNumber =
VAR _StartPeriod = MIN('Calendar'[Date])
VAR DurationDay=MAX('Calendar'[Date])-MIN('Calendar'[Date])+1
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])
return _MTDNumber
2nd measure is just a commulation of 1st measure.
aYTDNumber = CALCULATE('Calendar'[aMTDNumber],FILTER(ALL('Calendar'),'Calendar'[Date]<=MAX('Calendar'[Date])))
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.
Hi @amitksingh2003 ,
Could you pls share your sample data(remember to remove confidential data) ,and expect output?
Best Regards
Lucien