Forum Discussion

JC2022's avatar
JC2022
Icon for Helper III rankHelper 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 ...
  • 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]
    )
)
JC2022's avatar
JC2022
Icon for Helper III rankHelper 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
    Icon for Community Champion rankCommunity 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
      Icon for Helper III rankHelper 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?

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

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