Duration between two dates.

[topazz11]

My goal is to get the duration hours/ days between Keyout and Keyin.

I tried to rearrange the table in Power Query but it didn't work.

Is there a way to get the outpur using DAX or better Power Qeury way?

Anybody has any idea?
I appreciate any help in resolving this issue!

here is the pbix

pbix  

ids event car user date_time
dj7207 KeyOut 123 Curl 2023-07-08T18:16:14
49f017 KeyOut 87 Dee 2023-07-07T10:06:02
bu7i90 KeyIn 87 Chris 2023-07-06T10:44:26
bt0690 KeyIn 105 Jane 2023-07-06T10:40:40
09d806 KeyIn 123 Adam 2023-07-06T10:29:36
eff6c7 KeyIn 92 Matt 2023-07-06T11:04:03
450a1b KeyIn 101 Mark 2023-07-06T10:26:44
8bb6t5 KeyOut 92 Matt 2023-07-06T10:24:33
741c1c KeyOut 101 Mark 2023-07-06T09:53:34
pp6390 KeyOut 123 Adam 2023-07-06T09:43:27
b9533d KeyIn 117 June 2023-07-06T09:35:01
9866dc KeyOut 87 Chris 2023-07-06T09:30:56
8186c0 KeyIn 123 Matt 2023-07-06T09:11:28
79c6c1 KeyOut 105 Jane 2023-07-06T09:05:39
e91f4e KeyIn 87 Jess 2023-07-06T08:57:45
458d4b KeyOut 117 June 2023-07-06T08:54:46
e616aa KeyIn 101 Mark 2023-07-06T08:47:10
8p6390 KeyOut 101 Mark 2023-07-06T08:07:10
78b14e KeyOut 87 Jess 2023-07-05T05:50:45
f186c0 KeyOut 123 Matt 2023-07-04T07:11:28
d916a9 KeyOut 99 Kim 2023-07-03T11:11:28

[ppm1]

Please see this article. You may not even need a calcualted column.  If so, you can subtract the two columns in DAX or Power Query and format as a decimal (gives duration in days, but you can format it in your measures any way you want). 

Calculate and Format Durations in DAX – Hoosier BI

Pat

[ThxAlot]

duration.pbix

DAX solution is a bit tricky but more concise. Hope you have enough knowledge to comprehend it.

[topazz11]

Hi,

How do you habdle when the keyin but no date for keyout?

it gives me weird duration. any thughts?

Thanks

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"ids"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"car", "user", "event"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"date_time", "Index"}, {"date_time", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded All", List.Distinct(#"Expanded All"[event]), "event", "date_time"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"car", type text}, {"KeyOut", type datetime}, {"KeyIn", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Current time", each DateTime.LocalNow() as datetime),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Duration", each if [KeyIn]=null then [Current time] - [KeyOut] else [KeyIn]-[KeyOut]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}})
in
    #"Changed Type1"

Hope this helps.

[topazz11]

almost...but some lines are split as below. they need to be on the same row.

could you take a look ?

[Ashish_Mathur]

I cannot understand.  Show a representative sample and the expected result on that sample.

[topazz11]

you M code is very close but i am not sure why the user "Matt" has separate lines...it should have keyout and key in on the same row as below right side . 

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"ids"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"car", "user", "event"}, {{"All", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"date_time", "Index"}, {"date_time", "Index"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded All", {{"car", type text}, {"Index", type text}}, "en-IN"),{"car", "user", "Index"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[event]), "event", "date_time"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Car", "User"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Car", Int64.Type}, {"User", type text}, {"KeyOut", type datetime}, {"KeyIn", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Current time", each DateTime.LocalNow() as datetime),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Duration", each if [KeyIn]=null then [Current time] - [KeyOut] else [KeyIn]-[KeyOut]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", type duration}})
in
    #"Changed Type1"

[topazz11]

it doesnt work when the data is missing in either keyin or keyout

duration value doesnt make sense.. could you please check it one more time with this dataset?

here is new dataset

pbix 

thank you @Ashish_Mathur 

[Ashish_Mathur]

See the attached file.