Help with annualized attrition trend calculation

[RalucaS]

I've been struggling with this for a while now and I simply can't understand why I'm not getting the same numbers in PBI as in excel.

So, I have 2 data tables, 'Employee headcount EOM' with the employees at the end of each month, and 'Raw Data' with the leavers for each month. I need to calculate an annualized attrition trend based on the following logic: 

Monthly Average Headcount=(HC EOM previous month+HC EOM Current month)/2

Avg leavers YTD=total leavers YTD/Number of months YTD

Average Monthly Population=Monthly Average Headcount+Avg leavers YTD

Average Average Monthly Population YTD=Sum of Average Monthly Population YTD/Number of Months YTD

Total Leavers YTD=Sum of Leavers YTD

Average Attrition YTD=Total Leavers YTD/Average Average Monthly Population YTD

ANNUALIZED ATTRITION=Average Attrition YTD*12/Number of months YTD

Now my DAX:

Monthly headcount : Headcount = count('Employee headcount EOM'[Name]) 

Previous month headcount: 

Previous Month Headcount = calculate('Employee headcount EOM'[Headcount],DATEADD('Calendar'[Date],-1,MONTH))

Leavers = count('Raw data'[First name])

Leavers for each month: Leavers = count('Raw data'[First name])

Monthly Average Headcount = if([Previous Month Headcount]=0,round(2*('Employee headcount EOM'[Headcount])/2,0),round(('Employee headcount EOM'[Headcount]+[Previous Month Headcount])/2,0))

Average Leavers YTD = round(divide ( calculate ([YTD Leavers], DATESYTD('Calendar'[Date])),max('Raw data'[FY month no])),0)

Average monthly population = [Monthly Average Headcount]+[Average Leavers YTD]

YTD Leavers = TOTALYTD([Leavers],'Calendar'[Date],"31/03")

Average Average Monthly Population YTD = round(divide ( calculate ([YTD Average monthly population], DATESYTD('Calendar'[Date])),max('Employee headcount EOM'[FY month no])),0) - not getting the same numbers as in Excel

Average attrition YTD = [YTD Leavers]/[Average Average Monthly Population YTD] - not getting the same numbers as in Excel

Annualized attrition = [Average attrition YTD]*12/max('Employee headcount EOM'[FY month no])  - not getting the same numbers as in Excel

I've got my numbers correctly as far as Average Monthly Population and Total Leavers YTD, but once I get into the Average Average Monthly Population YTD, the numbers no longer reflect the same as I get in my excel calculations. The first month is correct for both PBI and excel. But starting May, I get slightly different values for the last 3 measures. 

Any ideas why PBI gives me different values? 

[MFelix · Accepted solution]

Hi @RalucaS 

Be aware that DAX calculations are based on context this means that a column, a filter, a slicer or any other thing can change the result of the calculations.

When using IF statments on your caclulations things get even funnier because when you go to aggregated levels you may not have the expected result.

Can you please share a mockup data or sample of your PBIX file and expected result (since you have the calculation in excel would be great to have that). You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.