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,
I have a data table tracking employees internal moves between departments.
Name | Department | Start Date | End Date |
[Employee Name] | [Department Name] | [First Day in Department] | [Last Day in Department - NULL IF ACTIVE] |
I'm now trying to create a measure to give me the average number of people working in a department at any given date (so that it'll work at date, month, and year level). Essentially this should just be a count of any record where the start date is less than or equal to the date, AND the end date is more than the date OR null.
I have a calendar table, but haven't created a relationship with this one yet as I'm unsure how to do this as I want to consider both Date columns in the calculation.
Is this something that's easily achievable?
Thanks,
Toby
Solved! Go to Solution.
Hi,
Please try the below measure.
Create a table visualization, and add one of columns from a calendar table (year, month, week, or date column).
And then add the below measure into the table visualization.
Employees count measure: =
CALCULATE (
COUNTROWS ( VALUES ( Table[Name] ) ),
FILTER (
Table,
Table[Start Date] <= MAX ( Calendar[Date] )
&& OR ( Table[End Date] >= MIN ( Calendar[Date] ), Table[End Date] = BLANK () )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please try the below measure.
Create a table visualization, and add one of columns from a calendar table (year, month, week, or date column).
And then add the below measure into the table visualization.
Employees count measure: =
CALCULATE (
COUNTROWS ( VALUES ( Table[Name] ) ),
FILTER (
Table,
Table[Start Date] <= MAX ( Calendar[Date] )
&& OR ( Table[End Date] >= MIN ( Calendar[Date] ), Table[End Date] = BLANK () )
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks so much! Exactly what I needed.
User | Count |
---|---|
48 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
102 | |
55 | |
28 | |
18 | |
14 |