Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there
I'm trying to return the count of staff that are employed on any given day/month/year. Table1 contains the staffID, StartDate and TerminatedDAte. Table2 is a date dimension. The current count is returning basically no results for all dates in 2018. Where have i gone wrong?
I want to be able to return '### staff were employed on 22/01/2018' or month on month active employee counts.
Count of Active Employees =
VAR currentdate =
MAX( 'Table2'[Date])
RETURN
CALCULATE(
[Staff ID Count],
FILTER (
Table1,
(Table1[StaffStartDate] <= currentDate
&& Table1[StaffTerminationDate] >= currentDate )
))
Hi,
How does the TerminatedDate look for staff still working? Do you account for blanks?
This might work in such a case:
Thanks Ahrne, i think you're onto something. I have not handled the blanks in the terminated date column.
Logic may need rework. What if someone's termination date was about a week ago and the currentdate is today. He will be counted as an active employee because you are filtering the table to allow any entries below the current date.
Hi @Anonymous,
i think there coud be a current filter context on Table1. So you could try applying your time yondition to All(Talbe1).
best regards
florian
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |