topic Re: Counting Employees at Specific Dates in DAX Commands and Tips

Counting Employees at Specific Dates

DH3612 — Thu, 16 Nov 2023 16:14:59 GMT

OK, Im new to the DAX/PowerBI thing, but ive done well so far in making it work. But I am stumped on this one.

I have a measure I created. Current Employees at Date. This should count the number of employees who are actively employed on a specific date. Ideally, the last day of a Month/Year that I can filter to.

My data comes from a table called Staffmaster, that has Columns Start Date, and End Date for employment

The DAX I used is as follows, but this doesnt seem quite right, if I filter to any date in the card I put this in, then it shows a really low number. If this is wrong, how d I amend this to show what I am after:

Employee Count at Date =

VAR _Start = MIN ( 'Date'[Date] )

VAR _End = MAX ( 'Date'[Date] )

RETURN

CALCULATE (

COUNTROWS ( Staffmaster ),

STAFFMASTER[Current Employment: Start Date] <= _End

&& ( staffmaster[End Date] >= _Start || ISBLANK ( staffmaster[End Date] ) )

)

Re: Counting Employees at Specific Dates

oritam3210 — Thu, 16 Nov 2023 21:39:09 GMT

Hi,
In your example,
you are calculating your MIN and MAX date from the Date table,
but on the DAX calculation, you are filtering the dates columns from STAFFMASTER table.
first -it will be good to align it.
second - what filter do you use to filter the card (from what table?)

Re: Counting Employees at Specific Dates

DH3612 — Fri, 17 Nov 2023 16:38:41 GMT

I think thats part of the issue. I was using the Date table to filter it.

However as an example - if we imagine this table is "Staffmaster" and asked how many people were employed at 28/02/2023, we should get 6. If we said how many employed 01/04/2023 we should get 9 and so on.

If my code isnt correct, is there an alternative?

Name	Start Date	End Date
John	01/12/2020
Babs	04/04/2021	06/04/2023
Nick	01/12/2019	01/08/2022
Ed	05/06/2018
Frank	06/09/2010
Bob	01/07/2009
Alfie	01/03/2023	05/08/2023
Jack	01/09/2021
Claire	27/01/2008

Re: Counting Employees at Specific Dates

oritam3210 — Sat, 18 Nov 2023 16:22:09 GMT

Hi,
I took the sample data and the same calculation,
it seems to be working.
the numbers should be "7" for the 28/02/2023 and "8" for the 01/04/2023

see examples
28/02/2023

01/04/2023

Re: Counting Employees at Specific Dates

DH3612 — Mon, 20 Nov 2023 09:35:50 GMT

What slicer/filter are you using? When I run it on the full dataset, I cant even get an answer close to what I expect.

The dax code is exactly as used in the first example. Maybe its how I filter/slice the data for this visual?

Re: Counting Employees at Specific Dates

DH3612 — Mon, 20 Nov 2023 10:48:44 GMT

oritam3210 I think its counting how many people "started" on any given day. Rather than how many were employed at a given date. I created a column chart, and that is what its implying. Isd there something wrong with the code then? or is there a better way to achieve what I wanted.

Re: Counting Employees at Specific Dates

Anonymous — Tue, 21 Nov 2023 02:36:36 GMT

Hi DH3612 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 = CALENDAR( DATE(2023,1,1), DATE(2023,12,31))

2. Create measure.

Measure = COUNTX( FILTER(ALL('Table'), AND( 'Table'[Start Date]<=MAX('Table 2'[Date]),OR('Table'[End Date]>=MAX('Table 2'[Date]),'Table'[End Date]=BLANK()))) ,[Name])

3. Result:

Best Regards,

Liu Yang

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

Re: Counting Employees at Specific Dates

oritam3210 — Tue, 21 Nov 2023 11:29:10 GMT

Hi,
It seems to work fine and counting the employees that worked on the selected period,

see PBIX file Counting Employees at Specific Dates.pbix

Calculations

Employee Count at Date = VAR _Start = MIN ( 'Date'[Date] ) VAR _End = MAX ( 'Date'[Date] ) RETURN CALCULATE( COUNTROWS ( Staffmaster ), STAFFMASTER[Start Date] <= _End && ( staffmaster[End Date] >= _Start || ISBLANK ( staffmaster[End Date] ) ) )

Date table

Date = ADDCOLUMNS( CALENDARAUTO(), "Year",YEAR([Date]), "Month",MONTH([Date]), "MonthName",FORMAT([Date],"MMMM") )

on filter / slicer / graph use only dates from Date table

---------------------------------------------------------------------------------------------------------------------
If you find this helpful, kindly consider marking it as 'Accepted Solution.'
This action helps others quickly find a reliable answer!