Current Staff and Turnover Not working

[petermb72]

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

[v-shex-msft · Accepted solution]

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

[petermb72]

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]

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