how do I calculate total duration from multiple rows by group

[yishenhui]

Hi,

I want to calculate date/time duration from mutliple rows and get the summary. Note those single durations may be covering part or full with others which those coverage can be calcualted once. The date/time is grouped by another field as well. For example below, group A has 4 time frames (we don't know the max rows before we load the orginal data). They are ordered by start date but datetime frame covered or separated each other. The result I want shuld be like the next screenshot. Can we get it?

Thanks.

[collinsg]

Good day yishenhui,

My interpretation of your question is that you wish to calculate, for each group, the duration in days between the maximum end date and minimum start date and your dates are in mm/dd/yyyy format. If this is the case the answer for A would be 9 so I’m not sure my interpretation is correct.

Even if my interpretation is not precise, an approach such as this may provide a solution.

1. Group by group, using the advanced options to add two columns, each summarised.
   1. Min Start
   2. Max End
2. Calculate the days between max end and min start.

The following code achieves that.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"group", type text}, {"Start date", type datetime}, {"End Date", type datetime}}),
    #"Grouped Rows" = Table.Group(
       #"Changed Type", 
       {"group"}, 
       {
          {"Min Start", each List.Min([Start date]), type nullable datetime}, 
          {"Max End", each List.Max([End Date]), type nullable datetime}
       }
    ),
    #"Add Days" = Table.AddColumn(
       #"Grouped Rows", 
       "Days", 
       each Duration.Days( [Max End] - [Min Start] ), 
       Int64.Type
    )
in

    #"Add Days"

Here is my data (at the step “Changed Type”)

Here is my result (my date format is dd/mm/yy)

Hope this helps.

[yishenhui]

Hi Collinsg,

It's not, there are two days from 4/7 to 4/9 not belongs to any single durations. That is what I want it being removed from total duration time. 7 is the one I want for this case.