Forum Discussion
YTD Attrition Calculation using Append Data Method
Hello Team,
I need help to do a calculation of YTD Attrition and Headcount - I was able to pull the numbers of attrition and headcount using replace data method and it is working good for the current year calculation but it is creating issues with the previous year with a few requirements such as Attrition and Headcount based on level and every year promotions are happening due to that levels are changing every 6 months but replace method is capturing only recent level or Job title - which is causing data inaccuracy due to that we have decided to use append method and put every year data on a dataset to show Visualization- Kindly suggest best practices to pull out numbers for Attrition and Headcount using append data method
Hi,
Have you followed the DAX formula posted by amitchandak to find the solution to your problem?
If so, would you like to mark his reply as a solution so that others can learn from it too?
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
Thanks in advance!
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
According to your description, I can roughly understand your requirement, you want to do a calculation of YTD Attrition and Headcount based on the dataset with the append data method, right? I think you can try this method to get the latest updated data and do the calculation based on it:
First, you can create 3 calculated columns in the table like this:
Year-H type = [Year]&[H type]Data update rank = RANKX('Table','Table'[Year-H type],,ASC,Dense)Is latest data = var _maxrank=CALCULATE(MAX('Table'[Data update rank]),ALL('Table')) return IF('Table'[Data update rank]=_maxrank,1,0)Then you can do the calculation based on the value of the column [Is latest data], you can just filter the table to the value 1 to get your desired output.
This is my sample DAX for you to make an imitation of your original DAX formula:
Headcount = COUNTX(filter(ALL('Table'),'Table'[Is latest data]=1),'Table'[Name])YTD Attrition = var _AttritionCount=CALCULATE(COUNT('Table'[Name]),FILTER(ALL('Table'),'Table'[Employee Type]="Ex-Employee"&&'Table'[Is latest data]=1)) return DIVIDE(_AttritionCount,[Headcount])And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , need to know data model for that. Please check the blog or attached file if that can help
you can try a measure like using blog measures
calculate([Terminated Employees], datesytd('Date'[Date]) )
Hi,
Have you followed the DAX formula posted by amitchandak to find the solution to your problem?
If so, would you like to mark his reply as a solution so that others can learn from it too?
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
Thanks in advance!
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Team,
Please find the below table for your reference.
Year H type Name Start Date Date of Termination Employee Type Level 2019 H1 Test_Person 1 2/1/2018 Employee Associate 2019 H1 Test_Person 2 5/7/2018 Employee Sr. Associate 2019 H1 Test_Person 3 10/2/2019 7/3/2019 Ex-Employee Manager 2019 H1 Test_Person 4 7/3/2019 Employee Director 2019 H2 Test_Person 1 2/1/2018 Employee Sr.Associate 2019 H2 Test_Person 2 5/7/2018 Employee Sr. Associate 2019 H2 Test_Person 3 10/2/2019 7/3/2019 Ex-Employee Manager 2019 H2 Test_Person 4 7/3/2019 Employee Director 2020 H1 Test_Person 1 2/1/2018 Employee Sr.Associate 2020 H1 Test_Person 2 5/7/2018 5/7/2020 Ex-Employee Sr. Associate 2020 H1 Test_Person 3 10/2/2019 7/3/2019 Ex-Employee Manager 2020 H1 Test_Person 4 7/3/2019 Employee Director 2020 H2 Test_Person 1 2/1/2018 Employee Sr.Associate 2020 H2 Test_Person 2 5/7/2018 5/7/2020 Ex-Employee Sr. Associate 2020 H2 Test_Person 3 10/2/2019 7/3/2019 Ex-Employee Manager 2020 H2 Test_Person 4 7/3/2019 Employee Sr.Director Year - Actual Year, H Type - Half Year, Employee Type - If an employee is still with Org or not, Designation -
Let me know if you need more details- we are updating the database every 6 months hence the same name will get repeated but the status may be changed - The termination date can add or Designation may change after promotion.
Thank you,
Ankush Raul.
Hi,
According to your description, I can roughly understand your requirement, you want to do a calculation of YTD Attrition and Headcount based on the dataset with the append data method, right? I think you can try this method to get the latest updated data and do the calculation based on it:
First, you can create 3 calculated columns in the table like this:
Year-H type = [Year]&[H type]Data update rank = RANKX('Table','Table'[Year-H type],,ASC,Dense)Is latest data = var _maxrank=CALCULATE(MAX('Table'[Data update rank]),ALL('Table')) return IF('Table'[Data update rank]=_maxrank,1,0)Then you can do the calculation based on the value of the column [Is latest data], you can just filter the table to the value 1 to get your desired output.
This is my sample DAX for you to make an imitation of your original DAX formula:
Headcount = COUNTX(filter(ALL('Table'),'Table'[Is latest data]=1),'Table'[Name])YTD Attrition = var _AttritionCount=CALCULATE(COUNT('Table'[Name]),FILTER(ALL('Table'),'Table'[Employee Type]="Ex-Employee"&&'Table'[Is latest data]=1)) return DIVIDE(_AttritionCount,[Headcount])And you can get what you want.
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
