Calculating a due date excluding weekend and holidays

DAX DAX Calculations
1

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!

2

@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.

4

And here’s another option:

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

Hi @LisaKBI1129 , did the response provided by @bradsmith help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

1 Like
6

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

image
image809Ă—317 9.47 KB

7

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[IsBusinessDay] = TRUE()
        ),
        Dates[Date]
    )
RETURN
    IF ( LOOKUPVALUE(Dates[IsBusinessDay], Dates[Date], Dates[Date] + 5) = FALSE(), NextWorkingDay, DATEADD( Dates[Date], 5, DAY ) )
8

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.

9

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