I'm curious as to how your date table works. I tested this and while it works if I select a SINGLE date, it doesn't seem to accept/handle a date range. I'm just not sure what I'm missing exactly.
I also went into your file and added a card jsut to show the total count of the timespan and it seems to be not giving me any number when it should in theory show the total count of people active at any point in time during that span. So this is what I'm probably missing on mine as well
Hi @tagban ,
Please update the formula of measure as below and check if can return the expected result...
Number of Active People =
VAR _seldate =
MAX ( 'Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[id] ),
FILTER (
'Table',
'Table'[enrollment_start] <= _seldate
&& (
'Table'[enrollment_end] >= _seldate
|| ISBLANK ( 'Table'[enrollment_end] )
)
)
)
Best Regards
Tried this out and it seems to be better, but still having some issues. I -think- I know what the problem is, I think the measure isn't accounting for the min side of date table. IE when you have a slider you're really getting a range of dates like a list right? So you'd look for enrollment start <= minimum(date), and enrollment end is >= max(date)
But the ttruth is, I actually don't care if they leave during that time, what we're looking for is the total people active during those two thresholds.
So for instance someone joins on 10/30/2023 and leaves on 2/25/2024. The slider would be set to 10/20/2023 - 11/2/2023
That person would be considered enrolled during that time. Despite starting after the start time, and ending after the end time.
The same would hold true with the same filter for someone that enrolled on 10/29/2023 and left on 11/1/2023, Still active at some point in that window.
Just testing the formula you provided unfortunately shows still the inaccuracy of the min date, but it seems to be closer to what I'd expect at the max (Single point in time).
Like anyone in the above window should also be in this next one:
Hi @tagban ,
Base on your description, it seems like you want to get the number of active people during the selected date period. You can create a measure as below to get it, please find the details in the attachment.
Number of Active People =
VAR _seldate =
SELECTEDVALUE('Date'[Date])
RETURN
SUMX (
'Table',
VAR peopleStartDate = [enrollment_start]
VAR peopleEndDate = [enrollment_end]
RETURN
IF (
peopleStartDate <= _seldate
&& OR ( peopleEndDate >= _seldate, peopleEndDate = BLANK () ),
1,
0
)
)
In additional, you can refer the following links to get it:
Power BI: Employee count by month tutorial - Finance BI (finance-bi.com)
Employee Count =
VAR selectedDate =
MAX ( 'Date'[Date] )
RETURN
SUMX (
'Employees',
VAR employeeStartDate = [Start Date]
VAR employeeEndDate = [End Date]
RETURN
IF (
employeeStartDate <= selectedDate
&& OR ( employeeEndDate >= selectedDate, employeeEndDate = BLANK () ),
1,
0
)
)How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX
powerbi - Calculating the number of active employees as at previous months with DAX
ActiveEmps :=
VAR ThisDate =
MIN( Dim_Date[Date] )
RETURN
CALCULATE(
COUNTROWS( employee ),
FILTER(
ALL( employee ),
employee[Start Date] < ThisDate
&& employee[Termination Date] > ThisDate
)
)Best Regards
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
