Forum Discussion

JC2022's avatar
JC2022
Helper III
3 years ago
Solved

Sum if value is between two dates

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

 

 

 

  • tamerj1's avatar
    tamerj1
    3 years ago

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

8 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi JC2022 

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

  • JC2022's avatar
    JC2022
    Helper III
    3 years ago

    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's avatar
      tamerj1
      Community Champion
      3 years ago

      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's avatar
        JC2022
        Helper III
        3 years ago

        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?