Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Help with ADDCOLUMNS function

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 _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

 

Need Help

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    Could you pls share your sample data(remember to remove confidential data) ,and expect output?

     

     

    Best Regards

    Lucien

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-luwang-msft , pls see the update to my original post for more context. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.