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,
I want to calculate the hours for each employee on each day (and sum these per period).
First I need to check the Schedule ID per Employee ID on each day, because this can change over time (as you can see below in Schedule table for Employee ID 59).
Then I need to calculate the correct hours belonging to the correct Schedule ID on a particular date for each Employee ID. This probably by checking if the date in my Hours table is between from date and to date in my Schedule table.
I would like to do this in a measure where the result for Employee ID 2 should be 5*8hours=40hours. And the result for Employee ID 59 should be 3*8hours=24hours. By filtering on the Date table the results should be recalculated as a measure does.
Can anyone help me with this measure formula?
There are 3 tables as below:
Schedule table:
Hours table:
Date table (calendar table, with every date):
Solved! Go to Solution.
@JC2022
Please try
=
SUMX (
Schedule,
SUMX (
FILTER (
Hours,
Hours[Schedule ID] = Schedule[Schedule ID]
&& Hours[Date] >= Schedule[From Date]
&& Hours[Date] <= Schedule[To Date]
),
Schedule[Hours]
)
)
hi @tamerj1,
See Hours table.
Employee ID 2 has Schedule ID 1, which is in the Hours table 5 times 8hours.
Employee ID 59 has Schedule ID 35, 51 and 70, which is in the Hours table 3 times 8 hours.
@JC2022
Please try
=
SUMX (
Schedule,
SUMX (
FILTER (
Hours,
Hours[Schedule ID] = Schedule[Schedule ID]
&& Hours[Date] >= Schedule[From Date]
&& Hours[Date] <= Schedule[To Date]
),
Schedule[Hours]
)
)
Thank you very much! It is working.
But I do have an additional question. When there is a Holiday table, with all the holiday days. How can I exclude these holiday dates from this formula?
Please try
=
SUMX (
Schedule,
SUMX (
FILTER (
Hours,
VAR Dates =
CALENDAR ( Schedule[From Date], Schedule[To Date] )
VAR Dates2 =
EXCEPT ( Dates, VALUES ( Holidays[Date] ) )
RETURN
Hours[Schedule ID] = Schedule[Schedule ID]
&& Hours[Date] IN Dates2
),
Schedule[Hours]
)
)
This is not working. The last mentioned table and column in your formula are Schedule[Hours], but my Schedule table does not have Hours as a column. I assume you are referring to my Hours table?
But even with this change it is not working. It is calculating for more than 10 minutes now (see my image below). Don't think this is correct.
after 15 minutes definite sign this is not working.
Indeed this is a very heavy calculation. It would work with a small set of data.
Is the Schedule ID in the Schedule table unique? If so you can build a relationship between the two tables. This by itself would make the calculation much faster and further shall open the door for further optimization.
No this Schedule ID in the Schedule table is not unique, because multiple Employee ID can have the same Schedule ID. What is the best solution to get the requested result?
Hi @JC2022
where did the 5 and the 3 come from? Any relationships between the tables?
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |