Find Overlap Days between two date ranges

[Anonymous]

hi team , please help me kindly with this challenging situation. I have a table with employee allocation details , each employee can have more than one tasks allocated to him with start and end date .

Eg : Employee 1 
task 1 : June 1st to June 20th 
task 2 : May 30th to June 16th . 

I need to calculate the number of working days ( Exclude sat & sun ) for this employee with task overlap dates should be distinct .  like june 1st is included in both date ranges so should be considered as one only not 2 days . 
can u help me with this scenario 

[slorin · Accepted solution]

Hi

List.Count(
  List.Select(
    List.Distinct(
      List.Dates([Start 1],Duration.Days([End 1]-[Start 1])+1,#duration(1,0,0,0)) 
      & List.Dates([Start 2],Duration.Days([End 2]-[Start 2])+1,#duration(1,0,0,0))),
    each Date.DayOfWeek(_,Day.Monday)<5)
  )
)

Stéphane

[slorin · Accepted solution]

Hi

List.Count(
  List.Select(
    List.Distinct(
      List.Dates([Start 1],Duration.Days([End 1]-[Start 1])+1,#duration(1,0,0,0)) 
      & List.Dates([Start 2],Duration.Days([End 2]-[Start 2])+1,#duration(1,0,0,0))),
    each Date.DayOfWeek(_,Day.Monday)<5)
  )
)

Stéphane

[Anonymous]

Thank you so much 🙂 . Should i execute this in Power Query as a seperate query or in DAX

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You can add a custom column with @slorin 's code in Power Query Editor. It should work if all start dates and end dates are on the same row for an employee. 

If every task has a single row like below, 

You can try this solution. Create a new query with below code to see how it works. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjVU0lFKBGIjAyNjBa/SvFRktoKRgVKsDlRVEkzGN7FSwdgARZ2hGUSdEVA0GaGuKDlDwQSVa2iAUJmCX6WFUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Task = _t, #"Start date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Task", type text}, {"Start date", type date}, {"End date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"Alldata", each _, type table [Employee=nullable text, Task=nullable text, Start date=nullable date, End date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.Count(List.Select(List.Distinct(List.Combine(List.Transform(Table.ToRecords([Alldata]), each List.Dates(_[Start date], Duration.Days(_[End date] - _[Start date])+1, #duration(1,0,0,0))))), each Date.DayOfWeek(_,Day.Monday)<5)))
in
    #"Added Custom"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.