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 attriti...
Need Help
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.