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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
petermb72
Helper III
Helper III

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

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
petermb72
Helper III
Helper III

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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