Forum Discussion

jst3000's avatar
jst3000
Regular Visitor
4 years ago
Solved

m code add business days to date

Currently have 3 tables Company Holidays Dates Tasks Looking to add a custom column "TASK_DUE_DATE" in the 'tasks' table that will show the next business day based on my "working days" column i...
  • lbendlin's avatar
    4 years ago

    You want an M code solution yet your sample file (thank you for providing that) shows a DAX calendar.  PLease clarify.   

    Can we assume that your weekend is Saturday/Sunday?

    Your tasks  table in DAX only has a start date.  What's the epected task duration SLA? One Business Day?

     

    In that case here is a purely M based solution:

     

    Holidays:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdLJDcMwDETRXnwOQIvaazHSfxuR5GE4PD7Q1rcSPs+VJIneqV3fz3OppOqqkm9Xl+KYQs+ldUKJ5KmK5shGFN3oyOvtWmdMF/IdecLu5UierkM1shLf24+DjPsOyw8X8gP57Ng9jeSp4mtGzA/Oz4MiabrWU92F/EReHbs3IumUf37G/KS83pa/XfV9EOrnwsCU7ti9FslT5Il0KvLJMFxr9WjWz7bZg1gvYiNaMcXinzk2z8cqrzv0/uLAu+6ALRiRp1h3YiOev06z5ZsL6w4hn2nd7S0sGJGnWHdiDcWNYqgu7BuEfKF9s7ewb8RJtF6Jt1/8/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
    in
        #"Changed Type"

     

     

    Tasks:

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRS0lEy1zfUNzIwgjBNIcxYHaCkmYkpWMwIq7SFiTlE2hirtKUFVLcJVmlTEzNDZDEQ0wzJbkMTsHYzfWMDhAKoO2NjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TASK_ID = _t, TASK_START_DATE = _t, TASK_DUE_DATE = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"TASK_ID", Int64.Type}, {"TASK_START_DATE", type date}, {"TASK_DUE_DATE", type date}}),
        LG = (d)=> List.Generate(()=>1,each _ < 11, each _ +1, each if Date.DayOfWeek(Date.AddDays(d,_),Day.Monday)<5 then Date.AddDays(d,_) else null),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Due Date", each List.RemoveNulls(List.Difference(LG([TASK_START_DATE]),Holidays[Date])){0})
    in
        #"Added Custom"

     

    It takes each start date, creates a list of the next ten days (replacing saturdays and sundays with nulls), and then removes all nulls and all items that are also appearing in the Holidays table. Finally it grabs the first item from the remaining list.