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.
Not sure why I never published this solution here to the QMG but better late than never I suppose. Couple measures to add working days to a date, one that accounts for holidays and one that doesn't. Also included in the PBIX is a column version (doesn't account for holidays) and a PQ version which also doesn't account for holidays. Could be easily adapted to do so.
Add Working Days Measure =
VAR __WorkDays = MAX('Table'[Work Days])
VAR __Days = __WorkDays * 4
VAR __StartDate = MAX('Table'[Start Date])
VAR __Calendar =
FILTER(
ADDCOLUMNS(
CALENDAR( __StartDate + 1, __StartDate + __Days),
"WeekDay",WEEKDAY([Date],2)
),
[WeekDay] < 6
)
VAR __Table =
ADDCOLUMNS(
__Calendar,
"Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
)
VAR __Result = MAXX(FILTER(__Table, [Index] = __WorkDays),[Date])
RETURN
__Result
Add Working Days Holidays Measure =
VAR __WorkDays = MAX('Table'[Work Days])
VAR __Days = __WorkDays * 4
VAR __StartDate = MAX('Table'[Start Date])
VAR __Calendar =
EXCEPT(
SELECTCOLUMNS(
FILTER(
ADDCOLUMNS(
CALENDAR( __StartDate + 1, __StartDate + __Days),
"WeekDay",WEEKDAY([Date],2)
),
[WeekDay] < 6
),
"Date", [Date]
),
'Holidays'
)
VAR __Table =
ADDCOLUMNS(
__Calendar,
"Index", COUNTROWS(FILTER(__Calendar, [Date] <= EARLIER([Date])))
)
VAR __Result = MAXX(FILTER(__Table, [Index] = __WorkDays),[Date])
RETURN
__Result
eyJrIjoiOThmZGExYTUtNmU0ZC00MWZiLWEyOGMtNzU3MWY0NDBiY2YzIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9