Forum Discussion
Helper IYTD Attrition
I'm trying to figure out a formula that will generate the YTD attrition, but that catch is the numbers must come from the employee list that i have. the excel report has employee ID, date of join and date of separation columns. i was able to generate the monthly attrition %, but this time the YTD needs to refer to the opening headcount of my fiscal year, which is april XXXX
Here's the formula.
YTD % = Total Separations from April to the selected slicer (for example Oct 2023) / Average of Opening Headcount of April and Closing Headcount of Oct 2023).
for now i need the DAX for Total Separations from April to the selected slicer (for example Oct 2023) and Opening Headcount of April
1 Reply
- AkameNoGamma
I asked chat GPT to generate the total separations based from my Fiscal Year (2024), but I can't get any numbers. here's my formula. This is from a post here and I tweak a bit of it based on my understanding. but still I cant produce the result I need.
Filters: Fiscal Year of 2024
Total Separations =VAR _min_date = MINX(ALL('Date'), 'Date'[Date])VAR _Expression =IF (ISFILTERED('Date'[Fiscal Year]),MAXX('Date', STARTOFMONTH(DATEADD('Date'[Date], 3 - MONTH('Date'[Date]), MONTH))),MAXX('Date', DATE(YEAR(MAX('Date'[Date])), 4, 1)))RETURNCALCULATE (COUNTX (FILTER ('Table 1 (Employee List)','Table 1 (Employee List)'[Effective Date of Seperation] >= _Expression&& 'Table 1 (Employee List)'[Effective Date of Seperation] <= _min_date),'Table 1 (Employee List)'[Local Emp ID]),CROSSFILTER('Table 1 (Employee List)'[Effective Date of Seperation], 'Date'[Date], None))
