DateAdd but only week days

[ZealandZu]

Hi All

Im having trouble visualising how i would carried out the following task. In Table 1 i have the task information like task number, the type, dates etc (the data is just representative and much much bigger)

Task#	Task Type	Start Date	Target Completion Date	Status
123	A	1/3/22	10/3/22	Closed
124	A	3/4/22	13/4/22	Open
125	B	15/5/22	10/6/22	Open

Then in Table 2 i have Task type infomation with all the different task types, the phase description, the hours required a day, amount of days etc

Task Type	Phase	Day	Daily Hours required	Cumulative hrs
A	Strip	1	10	10
A	Survey	2	8	18
A	Survey	3	8	26
A	Repair	4	12	38

Now in reality Task A last 100s of days and task b is even more etc. 

So initially I merged table 1 with 2 in order to get something like this and added a Date column using Date.AddDays([Start Date],[#"Day-1]) (i made a day minus one column otherwise the first date would of been the 2/3/22)

Task#	Task Type	Start Date	Phase	Day	Date	Hours
123	A	1/3/22	Strip	1	1/3/22	10
123	A	1/3/22	Survey	2	2/3/22	8
123	A	1/3/22	Survey	3	3/3/22	8
123	A	1/3/22	Repair	4	4/3/22	12
123	A	1/3/22	Repair	5	5/3/22	12
123	A	1/3/22	Build	6	6/3/22	10

The only problem with this though is that the 5th and 6th is on the weekend and no one would be in then to work. So Day 5 needs to Monday 7/3/22, Day 6 on the tues etc.

Now i do have a calendar table and that table does have whether the date is a working or non working date but i cant figure out how i add on a "Day" number to a date that excludes holidays and weekends.

Any ideas? Thanks

[amitchandak · Accepted solution]

@ZealandZu , I have DAX solution

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...6

The column solution is given in the comment.

[Anonymous · Accepted solution]

Hi  @ZealandZu ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can achieve it in Power Query Editor with below applied codes for Table1 query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgRiY31DfSMDIyMI0wDGds7JL05NUYrVAak1gao10TeGyZvoG8LZ/gWpeVCVpkCuExCb6huawKTNkIyFKI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Task#" = _t, #"Task Type" = _t, #"Start Date" = _t, #"Target Completion Date" = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Task#", Int64.Type}, {"Task Type", type text}, {"Start Date", type date}, {"Target Completion Date", type date}, {"Status", type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start Date", Int64.Type}, {"Target Completion Date", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Date", each {[Start Date]..[Target Completion Date]}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom1", "Date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Date",{{"Target Completion Date", type date}, {"Start Date", type date}, {"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Work Date", each if  Calendar[IsWorkday]{List.PositionOf(Calendar[Date],[Date])}=1 then Calendar[Date]{List.PositionOf(Calendar[Date],[Date])} else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Work Date] <> null))
in
    #"Filtered Rows"

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak · Accepted solution]

@ZealandZu , I have DAX solution

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...6

The column solution is given in the comment.

[ZealandZu]

Apologies i may be doing something wrong, but when i click on your solution link, all im seeing is a load of images in an album that dont seem to relate to my issues. thanks