Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
89 | |
75 | |
69 | |
65 | |
58 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |