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 (
            Hours,
            Hours[Schedule ID] = Schedule[Schedule ID]
                && Hours[Date] >= Schedule[From Date]
                && Hours[Date] <= Schedule[To Date]
        ),
        Schedule[Hours]
    )
)

  

  • 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

5 Replies

  • tamerj1's avatar
    tamerj1
    Community 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
      Helper 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
        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
  • rautaniket0077's avatar
    rautaniket0077
    Resolver I
    3 years ago

    Hi JC2022,
    you can use the below dax to exclude or include date.

    The Date column used in above table is from Holiday Table and if these dates are there is Orders table then are "Included" else "Excluded"

    Please accept my answer as solution if it solves your issue.