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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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