Forum Discussion
m code add business days to date
- 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.
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.