cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elegreen
Helper I
Helper I

DAx measure for average duraction over time

hello

 

I am trying to work out how long the average duration of employment for a group of employees across a large company is over time

 

I have a table called Current_Worker_Detail_Report

 

This includes

Name

Cost Centre

HireDate

EndDate

 

For employees still working here, EndDate is blank.

 

Each employee is present only once in the dataset.

 

I have a Date table called Calendar

 

I have this measure which calculates the number of staff employed on any day since the dataset begins and seems to work fine:

 

StaffCount =var CurrentDate= max('Calendar'[DATE])
RETURN
CALCULATE(
DISTINCTCOUNT(Current_Worker_Detail_Report[Censhare Author Name]),Current_Worker_Detail_Report[Hire Date]<=CurrentDate, OR(ISBLANK(Current_Worker_Detail_Report[End date]),Current_Worker_Detail_Report[End date]>CurrentDate))

 

How do I go about calculating length of employment on an ongoing basis? DATEDIFF but then what I am using as EndDate as when I tried this with EndDate as Max(Calendar'Date'), the output over all time calculated the duration as ongoing until today (when the figure for Dec 2019 would be duration up to end of that month etc, and then January would be a month longer etc etc)

 

very grateful for any pointers

6 REPLIES 6
v-eachen-msft
Community Support
Community Support

Hi @elegreen ,

 

Could you please share your sample data and expected result to me if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

I can't upload it but this is the premise

 

Made up sample data

MadeUp Ltd launched on 1/1/2019 with ten staff. Therefore the average duration of employment in Feb 19 was 1 month.

Then half the MadeUp team decided to leave all at the same time at the end of the year (traitors!) and the company hired five new staff to replace them. So the average length of employment was 11 months in December 2019, but fell to 6 months in January 2020 (5 people had 12 months duration and 5 people had 0)

 

So this but for a dataset of 12,000 people

 

NameCost CentreHireDateEndDate
Person 1Creative01/01/201901/01/2020
Person 2Creative01/01/201901/01/2020
Person 3Creative01/01/201901/01/2020
Person 4Creative01/01/201901/01/2020
Person 5Creative01/01/201901/01/2020
Person 6Creative01/01/2019 
Person 7Creative01/01/2019 
Person 8Creative01/01/2019 
Person 9Creative01/01/2019 
Person 10Creative01/01/2019 
Person 11Creative01/01/2020 
Person 12Creative01/01/2020 
Person 13Creative01/01/2020 
Person 14Creative01/01/2020 
Person 15Creative01/01/2020 

 

MonthAverageEmploymentDurationMonthsAverageNumberofStaff
Feb-19110
Mar-19210
Apr-19310
May-19410
Jun-19510
Jul-19610
Aug-19710
Sep-19810
Oct-19910
Nov-191010
Dec-191110
Jan-205.510
Feb-20710

 

Greg_Deckler
Super User
Super User

Not sure I am following, generally, IF(ISBLANK([End Date]),TODAY(),[End Date]) or if you want till the end of the current month, IF(ISBLANK([End Date]),EOMONTH(TODAY(),0),End Date])


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

What I am hoping to get out is something like this - say we had lots of fairly longstanding staff suddenly leave in February and be replaced by brand new staff, the averageduration would decline in March

 

DateAverageDurationOfEmployment
01/01/202015
01/02/202016
01/03/202010

 

If I use TODAY as the EndDate, the average duration for previous time periods will be incorrect, I think?  Because it would be calculating it to today, not until the end of the relevant month in the date column.

 

Does that make more sense?

 

thanks for your answer

@elegreen 

Refer, if My HR blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

some nice stuff there but nothing that seems to cover duration

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors