Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
AkameNoGamma
Helper I
Helper I

YTD 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 1
AkameNoGamma
Helper I
Helper I

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))
    )

RETURN
    CALCULATE (
        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)
    )

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.