Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) )
Solved! Go to Solution.
@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
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.
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]
)
)
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?
@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
Perfect!
Many thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
23 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |