DAX Formula for leavers in a rolling period (new hire turnover)

[JorisVanlommel]

Hi,

I have a data model with two tables (actually more but these are relevant for the question):

One table with employees and following columns:

- User ID

- Hire Date

- Company Exit date - if no exit this column is empty for that user id. 

- Seniority - where I added an extra column with categories (for example <1, 1-4, ... )

- Company

Seconde table with dates (=calender table).

I use a slicer on company and a timeline slicer. For a KPI - new hire turnover - i'm contructing measures. The KPI is calculated as follows. 

Leavers with a seniority less then 1 in a rolling periode of 1 year / Hires in that same rolling period of 1 year. 

To determine the start of the rolling period I use the timeline slicer which is divided in "months". If I select for example dec 2023 - the rolling period is defined as 31/12/2023 - 364 days so the actual period is 31/12/2023 untill 01/01/2023 for counting the hires and leavers with <1 year  

I added a measure for the hires in the rolling period - this seems to work fine - I used following DAX formula (in power pivot):

=CALCULATE (COUNT(Basic_User_Information___KPI___ESG__1[Hire Date]),DATESBETWEEN (Dates[DATES], LASTDATE(DATEADD(Dates[DATES],-365,DAY)), LASTDATE (Dates[DATES])))

I added a second measure for the leavers (but not yet with a filter on the seniority), but this doesn't work at all. It gives me data but I cannot figure out what the amount means and it doesn't give me correct data: 

=CALCULATE (COUNT(Basic_User_Information___KPI___ESG__1[Company Exit Date]),DATESBETWEEN (Dates[DATES], LASTDATE(DATEADD(Dates[DATES],-365,DAY)), LASTDATE (Dates[DATES])))

This is strange as in the end the measure should do exactly the same but for a different column. It should count the dates if the dates are part of a rolling period based on the timeline slicer. 

Anyone has a solution ? If you can add the seniority filter in the measure this would be nice ! 

regards,

[v-yangliu-msft]

Thanks for the reply from @some_bih , please allow me to provide another insight:
Hi  @JorisVanlommel ,

You can try the following dax:

Measure =
var _maxdate=
MAXX(FILTER(ALL('Dates'),'Dates'[DATES].[Month]=MAX('Dates'[DATES].[Month])),[DATES])
var _mindate=
EOMONTH(_maxdate,-13)
return
CALCULATE(
    COUNT('Basic_User_Information___KPI___ESG__1'[Company Exit date]),
    FILTER(ALL('Basic_User_Information___KPI___ESG__1'),
    'Basic_User_Information___KPI___ESG__1'[Company Exit date]>_mindate&&
    'Basic_User_Information___KPI___ESG__1'[Company Exit date]<=_maxdate))

If the results don't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[some_bih]

Hi @JorisVanlommel 

This below is without your model data and relationships.

one of your problems is what amount is retrived for second measure (related to column [Company Exit Date]) while for first measure related to coulumn [Hire Date] you can reconcile / figure out amounts)?

It could be that your Dates table (Calendar table) is connected via Hire Date column not Company Exit Date? This could be reason you have unexpected results. 

If yes, think about your model or measures. 

[JorisVanlommel]

Dear Some_Bih,

That could be the problem - in an other measure I have indeed incorporated a "relationship" in another measure which include the "company exit date". 

Not sure how to incorporate this feature in the new measure. 

This is the measure where I use the relationship - CALCULATE([Hire Headcount],USERELATIONSHIP(Basic_User_Information___KPI___ESG__1[Company Exit Date],Dates[DATES]))

I'm a power pivot noob; i'm constructing a HR KPI Dashboard from scratch with no knowledge so my data model etc will probably be very unlogical and inefficient, but i'm just trying to get things to work :).

Regards,