Forum Discussion

JC2022's avatar
JC2022
Helper III
3 years ago
Solved

Exclude dates from another table

Hi,  If I have the formula below but I would like to exclude the dates that are in my Holidays table (a different table). How do I do this? = SUMX ( Schedule, SUMX ( FILTER ( ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    JC2022 
    Just add 0 like:

    =
SUMX (
    Schedule,
    SUMX (
        FILTER (
            Hours,
            Hours[Schedule ID] = Schedule[Schedule ID]
                && Hours[Date] >= Schedule[From Date]
                && Hours[Date] <= Schedule[To Date]
                && NOT ( Hours[Date] IN VALUES ( Holidays[Date] ) )
        ),
        Hours[Hours]
    )
) + 0
tamerj1's avatar
tamerj1
Community Champion
3 years ago

JC2022 
Just add 0 like:

=
SUMX (
    Schedule,
    SUMX (
        FILTER (
            Hours,
            Hours[Schedule ID] = Schedule[Schedule ID]
                && Hours[Date] >= Schedule[From Date]
                && Hours[Date] <= Schedule[To Date]
                && NOT ( Hours[Date] IN VALUES ( Holidays[Date] ) )
        ),
        Hours[Hours]
    )
) + 0
JC2022's avatar
JC2022
Helper III
3 years ago

Perfect!

Many thanks!