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 ...
  • 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]
    )
)
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?

  • tamerj1's avatar
    tamerj1
    Community 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
      Helper 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.

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        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.