topic Re: Exclude dates from another table in DAX Commands and Tips

Exclude dates from another table

JC2022 — Tue, 29 Nov 2022 08:33:12 GMT

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

Re: Exclude dates from another table

tamerj1 — Tue, 29 Nov 2022 09:14:05 GMT

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

Re: Exclude dates from another table

rautaniket0077 — Tue, 29 Nov 2022 09:27:57 GMT

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.

Re: Exclude dates from another table

JC2022 — Tue, 29 Nov 2022 10:16:37 GMT

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?

Re: Exclude dates from another table

tamerj1 — Tue, 29 Nov 2022 10:43:32 GMT

JC2022
Just add 0 like:

Re: Exclude dates from another table

JC2022 — Tue, 29 Nov 2022 10:49:30 GMT

Perfect!

Many thanks!