topic Re: Sum if value is between two dates in DAX Commands and Tips

Sum if value is between two dates

JC2022 — Mon, 28 Nov 2022 15:53:35 GMT

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

Re: Sum if value is between two dates

tamerj1 — Mon, 28 Nov 2022 16:00:59 GMT

Hi JC2022

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

Re: Sum if value is between two dates

JC2022 — Mon, 28 Nov 2022 18:25:20 GMT

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.

Re: Sum if value is between two dates

tamerj1 — Mon, 28 Nov 2022 18:44:57 GMT

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

Re: Sum if value is between two dates

JC2022 — Mon, 28 Nov 2022 19:48:15 GMT

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?

Re: Sum if value is between two dates

tamerj1 — Mon, 28 Nov 2022 20:32:25 GMT

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

Re: Sum if value is between two dates

JC2022 — Mon, 28 Nov 2022 21:01:38 GMT

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.

Re: Sum if value is between two dates

tamerj1 — Mon, 28 Nov 2022 21:15:02 GMT

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.

Re: Sum if value is between two dates

JC2022 — Mon, 28 Nov 2022 21:21:42 GMT

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?