Current Staff and Turnover Not working

I have a few tables of data.

Date: Date table with a date for each day of the year going back to 1900

Data Table: Employee number, Hire Date, Termination Date

Links: Hire Date and Termination Date are linked to the Date Table

I have 2 Measures:

Current Staff =

CALCULATE(COUNTROWS(Terminated_export),

Filter(VALUES(Terminated_export[HireDate]),Terminated_export[HireDate]<= MAX('Dates'[Date].[Date])),

FILTER(Values(Terminated_export[Termination Date]), or (Terminated_export[Termination Date]>=Min('Dates'[Date].[Date]),ISBLANK(Terminated_export[Termination Date]))))

Staff Turnover =

Measure staff Turnover =

CALCULATE(COUNTROWS('Terminated_export'),

FILTER(VALUES(Terminated_export[Termination Date]),Terminated_export[Termination Date]<=MIN('Dates'[Date])),

Terminated_export[Termination Date]<>BLANK())

When I put the data in a table, I get the number of staff hired on a given day, but it does not add them up as i move twoards my most current date. It just shows how many were hired each day. I do get a total at the end, but it does not account for any Terminated it just keeps adding the new employees.

What I get for the second measure is nothing at all.

I am suspecting something is up with either the linking of the dates, or else I am just not getting the formula right when I copy it from Youtube!!!!

Help!!

please

Need Help

Anonymous
5 years ago
HI petermb72,

It seems like a common date fields analysis requirement, I'd like to suggest you take a look at the following link 'start date' 'end date' part if helps.

Before You Post, Read This

If above not help, please share some dummy data to test:

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

3 Replies

Anonymous
Not applicable
5 years ago
petermb72

Please, do not try to re-invent the wheel. Read this: Calculate New, Returning, Lost, and Recovered Customers in #dax - SQLBI

Once you're on www.sqlbi.com use the Search facility to find what you need.
petermb72
Helper IV
5 years ago
So, I found out if I delete the links to the date table the counts work correctly. Not sure why, but it does. I am going to carry on and see where this goes. Any insight on why removing the link to the date table makes a difference in the count?
Anonymous
Not applicable
5 years ago
HI petermb72,

It seems like a common date fields analysis requirement, I'd like to suggest you take a look at the following link 'start date' 'end date' part if helps.

Before You Post, Read This

If above not help, please share some dummy data to test:

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

Forum Discussion

Current Staff and Turnover Not working

3 Replies

Related content

Incorrect turnover total

Cumulative turnover

Turnover over years

12 month rolling turnover

HR Turnover Rate