Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Pierrev31
Frequent Visitor

Calculate Card Total Days with Subtickets with Overlapping Dates

Hello everyone, 

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

Pierrev31_3-1656083819466.png

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! 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1656473730880.png

 

2. Group by ID Card column.

vjingzhang_1-1656473862950.png

 

3. Expand All Data column. 

vjingzhang_2-1656473916858.png

 

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

View solution in original post

1 REPLY 1
v-jingzhang
Community Support
Community Support

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. 

vjingzhang_0-1656473730880.png

 

2. Group by ID Card column.

vjingzhang_1-1656473862950.png

 

3. Expand All Data column. 

vjingzhang_2-1656473916858.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors