YTD Attrition Calculation using Append Data Method

[Ankush_Raul1991]

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

[v-robertq-msft · Accepted solution]

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.

[v-robertq-msft · Accepted solution]

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.

[v-robertq-msft · Accepted solution]

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.

[Ankush_Raul1991]

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.

[amitchandak]

@Ankush_Raul1991 , 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]) )

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...