We're giving away 30 tickets for FREE! Share your story, your vision, or your hustle and tell us why YOU deserve a ticket.
Apply nowWin a FREE 3 Day Ticket to FabCon Vienna. Apply now
I want to calculate headcount for the chosen End of Month (Nov-22) in this case. An employee can hae multiple date_to and Date_from records which captures the change of contract. Here is the situation:
For the above sample data
Total Employees =
VAR min_snapshot = CALCULATE(MIN(Parameter[End of Month]), ALLSELECTED())
VAR max_snapshot = CALCULATE(MAX(Parameter[End of Month]), ALLSELECTED())
VAR max_snapshot_2 = CALCULATE(MAX(Parameter[End of Month])+7, ALLSELECTED())
RETURN
CALCULATE(
DISTINCTCOUNT(unitt_hrreport_employee[user_id]),
UNION(
CALCULATETABLE(
VALUES(unitt_hrreport_employee[user_id]),
(
((unitt_hrreport_employee[date_from] > max_snapshot && unitt_hrreport_employee[date_from] < max_snapshot_2)
&& (unitt_hrreport_employee[date_to] > max_snapshot_2 || ISBLANK(unitt_hrreport_employee[date_to]) = TRUE))
),
FILTER(unitt_hrreport_employee, unitt_hrreport_employee[min_date_from_per_user_id] <= max_snapshot)
),
CALCULATETABLE(
VALUES(unitt_hrreport_employee[user_id]),
(
(unitt_hrreport_employee[date_from] <= max_snapshot
&& (unitt_hrreport_employee[date_to] > max_snapshot || ISBLANK(unitt_hrreport_employee [date_to] ) = TRUE)
)
))))
@tamerj1 @amitchandak @johnt75 @andhiii079845 @FreemanZ @Greg_Deckler Please help
User | Count |
---|---|
14 | |
12 | |
7 | |
7 | |
6 |
User | Count |
---|---|
28 | |
20 | |
14 | |
11 | |
5 |