# Calculating a due date excluding weekend and holidays

I need help calculating a hard due date. A vendor has 5 days to complete a task from initial start date. I have a robust date table with a calculated column for Weekends and one for Holidays. How would I write the dax to give the actual due date (start date + 5 - Weekend Column (BIT) and Holiday Column (BIT)?
Thanks!

@BrianJ did a great video on this topic.

Question, do you want this to be a calculated column or calculated measure?

If at all possible, could you add a sample pbix file for us to work with so we can use the same fields and table names you use in your file? This is a pretty straight forward question so I or someone in the forum should be able to get you something shortly, but itâ€™s always easier to work with the same data so we can make sure the function works with your data more easily.

And hereâ€™s another option:

WorkingDayPlus5 =
VAR NextWorkingDay =
MINX (
FILTER (
Dates,
EARLIER ( Dates[Date] ) + 5 < Dates[Date]
),
Dates[Date]
)
RETURN
IF ( LOOKUPVALUE(Dates[IsBusinessDay], Dates[Date], Dates[Date] + 5) = FALSE(), NextWorkingDay, DATEADD( Dates[Date], 5, DAY ) )
2 Likes

1 Like

My date table is named Dates, and my IsWorkday column is named CountableDay. What am I missing?

Try it as a calculated column.

I just realized I forgot something in the RETURN statement haha hereâ€™s the updated formula:

WorkingDayPlus5 =
VAR NextWorkingDay =
MINX (
FILTER (
Dates,
EARLIER ( Dates[Date] ) + 5 < Dates[Date]
),
Dates[Date]
)
RETURN
IF ( LOOKUPVALUE(Dates[IsBusinessDay], Dates[Date], Dates[Date] + 5) = FALSE(), NextWorkingDay, DATEADD( Dates[Date], 5, DAY ) )

Iâ€™ve added as a calculated column in my Dates table . However, Iâ€™m dealing with a Date Table (where my CountableDay column is location) and a Fact Table which has my â€śstart dateâ€ť that I need to calculate + 5 days. Its returning a date, but not the correct date.

I just updated the formula a little bit ago after trying the formula in a different report and noticing the issue. I think that fix should solve that issue. For your tables it should look something like this:

WorkingDayPlus5 =
VAR NextWorkingDay =
MINX (
FILTER (
Dates,
EARLIER ( Dates[Date] ) + 5 < Dates[Date]
&& Dates[CountableDay] = 1
),
Dates[Date]
)
RETURN
IF ( LOOKUPVALUE(Dates[CountableDay], Dates[Date], Dates[Date] + 5) = 0, NextWorkingDay, DATEADD( Dates[Date], 5, DAY ) )

That updated formula should return the correct date in date table for you, let me know if this still doesnâ€™t work.

1 Like