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:
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])
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@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.
It seems to work fine and counting the employees that worked on the selected period,
see PBIX file Counting Employees at Specific Dates.pbix
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 = 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!
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|
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
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?
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?)
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.