Forum Discussion
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 in the 'Dates' table
Sample file
https://drive.google.com/file/d/17uDyVQU0meBZo4jbcDlH6kU6d_WzkzKO/view?usp=sharing
Dates table image
Output Expectations
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.
1 Reply
- lbendlinSuper User
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.