Counting Employees at Specific Dates

[DH3612]

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] ) )

    )

[v-yangliu-msft]

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

[DH3612]

@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 

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!

[DH3612]

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/2023

01/04/2023

[DH3612]

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]

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