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

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
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’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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors