Sum if value is between two dates

[JC2022]

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):

[JC2022]

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.

[tamerj1 · Accepted 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]
    )
)

[JC2022]

@tamerj1 

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?

[tamerj1]

@JC2022 

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]
)
)

[JC2022]

@tamerj1 

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.

[tamerj1]

@JC2022 

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. 

[JC2022]

@tamerj1 

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?

[tamerj1]

Hi @JC2022 

where did the 5 and the 3 come from? Any relationships between the tables?