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.
Based on the current date I want to add 1 day, but skip weekends and holidays.
Examples
What measure can I use for this?
I have the table Holiday with the columns Date and Holiday. The column Date is the date of the holiday, the column Holiday has value 1 if the date is a holiday.
Additionally, I would like to have two variants of the same. Variant 1 = Add 2 days; Variant 2 = Add 3 days
Solved! Go to Solution.
I adapted it to my table and column naming and added the calculated column, but it did not work. It was loading infinitely. But thanks for the try.
The following measure works for me though:
Measure 1 =
VAR CurrentDate = TODAY()
RETURN
IF (
CurrentDate > EOMONTH (CurrentDate, 0),
BLANK (),
CALCULATE (
MIN ('Date'[Date]),
FILTER (
'Date',
'Date'[Date] > CurrentDate
&& 'Date'[IsWeekend] = 0
&& 'Date'[IsHoliday] = 0
)
)
)
hi @TimmK
Supposing 20th and 21st are holiday, try to add a calculated column like:
Column =
MINX(
FILTER(
Dates,
Dates[IsHoliday]=0
&&NOT WEEKDAY([Date],2) IN {6,7}
&&Dates[Date]>EARLIER(Dates[Date])
),
Dates[Date]
)
it worked like:
I adapted it to my table and column naming and added the calculated column, but it did not work. It was loading infinitely. But thanks for the try.
The following measure works for me though:
Measure 1 =
VAR CurrentDate = TODAY()
RETURN
IF (
CurrentDate > EOMONTH (CurrentDate, 0),
BLANK (),
CALCULATE (
MIN ('Date'[Date]),
FILTER (
'Date',
'Date'[Date] > CurrentDate
&& 'Date'[IsWeekend] = 0
&& 'Date'[IsHoliday] = 0
)
)
)
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |