finding Date Difference grouped

[dejadpower]

Hello there,

My sample data is

desired output is :

i could achieve the earliest start and latest end. But couldn't achieve Date difference is as 6 .

the measure i tried takes entire date difference between jan 1 to dec 31 .  Anybody please guide .

thank you

[v-tangjie-msft]

Hi @dejadpower ,

Thanks @JamesFR06  for the quick reply and solution. Here are some of my additions:

You can use the DATEDIFF function to calculate the number of days between measures.

Days = DATEDIFF([Earliest_Start],[Lastest_End],DAY)

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. 

[dejadpower]

Hi @v-tangjie-msft  , Thanks for helping.

for data:

Earliest Start : 04/01/2023

Latest End : 12/31/2023

The formula

DATEDIFF([Earliest_Start],[Lastest_End],DAY)

will give 360 days

But the actual working days of resources are 6 days.

kindly help to ahieve this

[v-tangjie-msft]

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. 

[dejadpower]

I'm getting error Cannot convert date to text in step 

Split Column by Delimiter

[v-tangjie-msft]

Hi @dejadpower ,

The split step is due to the fact that I need to adjust the date formatting. If your date is correct, you just need to refer to these steps below. Click on the gear to see the step details.

1.Add [end time2] column.

2.Group rows.

3.Add a custom column.

4.Change the [Custom] column type.

5.Remove Columns.

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. 

[JamesFR06]

HI

Earliest_Start=calculate(min(Start Time), all(tablewithdata))

Lastest_End= calculate(max(Start Time), all(tablewithdata))

Days=[Lastest_End]-[Earliest_Start]

[dejadpower]

Thank you @JamesFR06 

This method, i have already tried. it will take difference of Jan 04 to Dec 29 - which is 360 days

the expected output is 04/01 to 06/01 + 29/12 to 31/12  , which is 6 days in total

the second row difference should be skipped, because it is already covered in (29/12 to 31/12)

the 3rd , 4th , 5th rows difference should be skipped, because it is already covered in (04/01 to 06/01)

instead of taking all dates, it should cover the existing from , to date difference only.

Please guide