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 All,
I came across a very simple - complex (pardox) situation. I have a table like the below one with Emp_ID, Joining Date & Relieving Date with 5000 employee records for the last 20 years. I need to get a Monthly headcount report where a person has to be joined before the selected month and not relieved that month.
Emp_ID | Joining Date | Relieving Date |
Deva
Solved! Go to Solution.
You need a calendar table and a measure like
headcount = COUNTAX ( FILTER ( ALL ( Employees ), Employees[Joining Date] <= MAX ( CalendarTbl[MonthEnd] ) && ( Employees[Relieving Date] > MAX ( CalendarTbl[MonthEnd] ) || ISBLANK ( Employees[Relieving Date] ) ) ), Employees[Emp_ID] )
Dataset
C
You need a calendar table and a measure like
headcount = COUNTAX ( FILTER ( ALL ( Employees ), Employees[Joining Date] <= MAX ( CalendarTbl[MonthEnd] ) && ( Employees[Relieving Date] > MAX ( CalendarTbl[MonthEnd] ) || ISBLANK ( Employees[Relieving Date] ) ) ), Employees[Emp_ID] )
Dataset
C
Hi @Deva002. Could you provide some clarification with an example? If Emp_ID 123 has a joining date of 1 June 2016 and a relieving date of 1 August 2016, they would contribute to the headcount for only the month of July 2016. However, if they joined any time in May 2016, they should contribute to the headcount for June and July 2016. Is that accurate?
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |