Forum Discussion

Anett_R's avatar
Anett_R
Frequent Visitor
2 years ago
Solved

Subtract working days from date column

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!

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago

    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
            )

3 Replies

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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
            )
  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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