cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## 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:

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] ) )
)
7 REPLIES 7
Community Support

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

Frequent Visitor

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

Helper I

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 =
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!

Frequent Visitor

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
Helper I

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

Frequent Visitor

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?

Helper I

Hi,
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?)

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors