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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
topazz11
Helper III
Helper III

Duration between two dates.

 

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!

 

11Screenshot 2023-07.jpg

 

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

1 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"

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
ppm1
Solution Sage
Solution Sage

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

Microsoft Employee
ThxAlot
Super User
Super User

duration.pbix

 

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

ThxAlot_0-1688810942548.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



Hi,

 

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

it gives me weird duration. any thughts?

Thanks11Screenshot 2023-07sccx.jpg

Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 

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

could you take a look ?

 

Screenshot 2023-07-10 075gg.png

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 . 

 

11Screenshot 2023-07s.jpg

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"

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 

 

Screenshot 2023-07-11 14efe.png

 

thank you @Ashish_Mathur 

See the attached file.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.