Forum Discussion
Counting Employees at Specific Dates
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:
7 Replies
oritam3210Helper I
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?)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 - oritam3210
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/202301/04/2023
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?
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.
- oritam3210
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
CalculationsEmployee 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!
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
