topic Re: Needing help with New measure using date range in DAX Commands and Tips

Needing help with New measure using date range

KeyFMSPM — Fri, 28 Oct 2022 18:28:34 GMT

I struggle with DAX big time and I've been struggling with trying to create a new measure that establishes the number of active users based on a "Hire date" and "Termination date". What DAX function can I use to find active employee count if I only have hire and term dates? I am assuming that once I have this measure, I can use a slicer, for a specific period in time, and have an accurate headcount on said date. Any help is appreciated.

Re: Needing help with New measure using date range

amitchandak — Sat, 29 Oct 2022 03:13:41 GMT

KeyFMSPM , refer to my blog or video. Check the attached files

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Re: Needing help with New measure using date range

daXtreme — Sun, 30 Oct 2022 08:00:04 GMT

Hi KeyFMSPM

There are myriads of questions on this forum related to your problem or even straight answers to it. There's a Search box in the upper right corner of the page. Please make use of it. On top of that, I'd strongly suggest that you read this first: How to Get Your Question Answered Quickly - Microsoft Power BI Community

It will seriously help you obtain good and quick solutions.

Re: Needing help with New measure using date range

v-zhangti — Mon, 31 Oct 2022 02:58:13 GMT

Hi, KeyFMSPM

You can try the following methods.
Sample data:

Create a new calendar table.

Date = CALENDAR(MIN('Table'[Hire date]),TODAY())

Measure:

Measure = CALCULATE ( COUNT ( 'Table'[Employee] ), FILTER ( ALL ( 'Table' ), [Termination date] = BLANK () ) ) + CALCULATE ( COUNT ( 'Table'[Employee] ), FILTER ( ALL ( 'Table' ), [Hire date] >= MIN ( 'Date'[Date] ) && [Termination date] <= MAX ( 'Date'[Date] ) && [Termination date] <> BLANK () ) )

Please refer to the attachment.

Best Regards,

Community Support Team _Charlotte

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

Re: Needing help with New measure using date range

KeyFMSPM — Mon, 31 Oct 2022 14:47:58 GMT

This did the trick! Thank you.