Calculate Card Total Days with Subtickets with Overlapping Dates

[Pierrev31]

Hello everyone, 

I'm facing the current scenario and I need to replicate the columns Check Overlap and Days to Subtract in PowerBI:  

Every Main Card (ID Card) can have several subtickets (ID Subticket) for every communication made with differrent areas (Contacted Area Column). 

Every time a communication is made, a new Subticket is created, with a new creation date. When the contacted area answers, the return/Answer Date is filled manually, and when the subticket ends its communication, the end date is filled automatically. 

The adjusted end date either picks the return date (when its filled) or the end date, and if there's no end date, then TODAY.

What I need to replicate are the check overlap and days to subtract Column.

In the example above:

-The first subticket was created on the 23/05 and ended on the 23/05 (as the adj. end date shows), thus it stayed 0 days in communication, as it ended on the same day. 

-The second subticket was created on the 26/05 and is still open, so its 29 days in communication.

-The third subticket was created on the 02/06 and is also open, so its been 22 days in communication. However, it was created after the second subticket started, so all of its 22 days has been overlapped by the second ticket, which is why the check overlap column was created, to see how many days for each subticket were overlaping with the one before.

So, in this case, since the first subticket stayed 0 days in communication and all 22 days of the third subticket are being overlapped by the second subticket, then I only count the 29 days to see how many days the Main Card has been in communication. If there was no overlap, then I would add all communications to see the total days for every Main Card. 

Any help would be much appreciated! 

[v-jingzhang · Accepted solution]

Hi @Pierrev31 

Here is my solution. Create a blank query in Power Query Editor, open its Advanced Editor and paste below code to replace any code there. Save the code, check every step operation in Applied Steps pane. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcy7DcAgEAPQVU7UIGHzC9dlDkSRIgtk/yIfRRGhOtnyu9ZMrjDWkNVfZz32TZ7sSZccg8BroqKMpen2hpwgh00WFMWi+L5lx/jCMMEwbARRQWX5u34C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID Subticket" = _t, #"ID Card" = _t, #"Contacted Area" = _t, #"Creation Date" = _t, #"Adj. End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID Subticket", Int64.Type}, {"ID Card", Int64.Type}, {"Contacted Area", type text}, {"Creation Date", type datetime}, {"Adj. End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Communication Dates", each List.Dates(Date.From([Creation Date]), Duration.Days([Adj. End Date] - Date.From([Creation Date])), #duration(1,0,0,0))),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"ID Card"}, {{"All Data", each _, type table [ID Subticket=nullable number, ID Card=nullable number, Contacted Area=nullable text, Creation Date=nullable datetime, Adj. End Date=nullable date, Communication Dates=list]}, {"Communication Dates Count", each List.Count(List.Distinct(List.Combine([Communication Dates])))}}),
    #"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"ID Subticket", "Contacted Area", "Creation Date", "Adj. End Date"}, {"ID Subticket", "Contacted Area", "Creation Date", "Adj. End Date"})
in
    #"Expanded All Data"

The main three steps are 

1. Add a custom column to have a list of communication dates on every row. 

2. Group by ID Card column.

3. Expand All Data column. 

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