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

      • JC2022's avatar
        JC2022
        Icon for Helper III rankHelper III
        3 years ago

        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.