finding Date Difference grouped

Hi dejadpower ,

Click "transform data" to enter the power query editor to copy a table, and then open the "Advanced Editor" to copy and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkotzi8tSk5VMFTSUTKy1Dc00jcyMDIGcowN4ZxYHSSFRugKkTgoCkFyBib6BoYwhUgcDBOJUmiCodAMoTAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Resource = _t, #"Start Time" = _t, #"End Time" = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Start Time", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Start Time.1", "Start Time.2", "Start Time.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Resource", type text}, {"Start Time.1", Int64.Type}, {"Start Time.2", Int64.Type}, {"Start Time.3", Int64.Type}, {"End Time", type text}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Start Time.3", type text}, {"Start Time.2", type text}, {"Start Time.1", type text}}, "en-US"),{"Start Time.3", "Start Time.2", "Start Time.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "Start Time"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Start Time", type date}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "End Time", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"End Time.1", "End Time.2", "End Time.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"End Time.1", Int64.Type}, {"End Time.2", Int64.Type}, {"End Time.3", Int64.Type}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type2", {{"End Time.3", type text}, {"End Time.2", type text}, {"End Time.1", type text}}, "en-US"),{"End Time.3", "End Time.2", "End Time.1"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns1",{{"Merged", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type3",{{"Merged", "End Time"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "end time 2", each Date.AddDays([Start Time],3)),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Start Time", "end time 2"}, {{"Table",each _}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Duration.Days(List.Max([Table][#"End Time"])-
List.Min([Table][#"Start Time"]))+1),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"end time 2", type date}, {"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type4",{"Table"})
in
    #"Removed Columns"

Then we get two tables.

Click "Close and Apply".

Then we can create measures.

Earliest_Start = CALCULATE(MIN('Table (2)'[Start Time]),ALL('Table (2)'))

Lastest_End = CALCULATE(MAX('Table (2)'[End Time]), ALL('Table (2)'))

Days = CALCULATE(SUM('Table'[Custom]),ALL('Table'))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.