Forum Discussion

JC2022's avatar
JC2022
Icon for Helper III rankHelper 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
Icon for Community Champion rankCommunity Champion
3 years ago

Hi JC2022 
Please try

=
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]
    )
)
  • JC2022's avatar
    JC2022
    Icon for Helper III rankHelper III
    3 years ago

    Hi tamerj1 

    this is indeed excluding the dates, so it is not even showing the dates anymore. It's probably visually better to still show these dates but just return 0hours on these holiday dates. 

    What do you think? and how to do this?

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity 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
        Icon for Helper III rankHelper III
        3 years ago

        Perfect!

        Many thanks!