cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors