Subtract working days from date column

[Anett_R]

Hello everybody,

I have the following issue: 

I have a date from which I have to subtract the shipping time (in working days) to determine the planned shipping day.

In this example, the calculated shipping date should be the 14/06/2024, because the weekend shouldn't count.

Further restriction: the shipping time (working days) is variable - so in this case, it is 5 working days, but in another it could be e.g. 12 or 2.

Has anyone an idea of a workaround solution? Or is there a better way via DAX?

Thanks in advance!

[dufoq3]

Hi @Anett_R, different solution without recursive function:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNzDTNzIwMlHSUTJVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"shipping time (working days)" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"date", type date}, {"shipping time (working days)", Int64.Type}}),
    Ad_ShippingDate = Table.AddColumn(ChangedType, "shipping date", each 
        [ a = [#"shipping time (working days)"],
          b = List.Dates(Date.AddDays([date], -(a + Number.RoundUp(a/7)*2)), 3, #duration(1,0,0,0)),
          c = List.Select(b, (x)=> not List.Contains({5,6}, Date.DayOfWeek(x, Day.Monday))){0}?
        ][c], type date)
in
    Ad_ShippingDate

[Anett_R]

@AlienSx thank you so much - it works perfectly!

[AlienSx · Accepted solution]

@Anett_R this function should solve your problem in PQ

    wd = (delivery_date, days) => 
        if days = 0 
            then delivery_date 
            else @wd(
                Date.AddDays(delivery_date, -1), 
                if List.Contains({0, 6}, Date.DayOfWeek(delivery_date, Day.Monday)) 
                then days 
                else days - 1
            )