Forum Discussion
Solved
Subtract date/time in different rows with a condition
Hey guys, I have a sheet with "Processess movement" (I have no idea how you call it in english, so I just free translated it :-)". So, in each row of my sheet I have: num of the protocol | depa...
Hi joaocarlos95,
Based on my test, you could refer to below steps:
Add group index in query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY9bCgMxCEW3EvI9EK+PTOJWhux/G00KTRnGUvDr6PHqdeXTulI+MhNaIRTSRN0FThJR0TyOwGoJ1Y2c66ZSwAnkWn3OhtZ7q87STXU2Fp27qP3MMrfukEfWsuq2cM/C8y+c6262dXdkoX37N5qo/dEQafyhY7wA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"Time(send)" = _t, #"Time(recive)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Time(send)", type datetime}, {"Time(recive)", type datetime}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"all data", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded all data" = Table.ExpandTableColumn(#"Grouped Rows", "all data", {"Time(send)", "Time(recive)", "Index"}, {"all data.Time(send)", "all data.Time(recive)", "all data.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded all data",{{"all data.Time(send)", "Time(send)"}, {"all data.Time(recive)", "Time(recive)"}, {"all data.Index", "Index"}}) in #"Renamed Columns"Result:
Apply it and create two measures:
previous recive time per ID = CALCULATE(SELECTEDVALUE(Table1[Time(recive)]),FILTER(ALLEXCEPT(Table1,Table1[Id]),Table1[Index]=MAX(Table1[Index])-1))
time diff = DATEDIFF(SELECTEDVALUE(Table1[Time(send)]),[previous recive time per ID],MINUTE)
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hey Daniel, thank you for answering. Unfortunately it didn't work on my case because I need to subtract from the previously row and there's another issue that in a lot of cases I have 30 rows with the same ID.
However, I really like your formula and I'm using it in another data :-).
v-danhe-msft
Microsoft Employee
Microsoft EmployeeHi joaocarlos95,
Based on my test, you could refer to below steps:
Add group index in query editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY9bCgMxCEW3EvI9EK+PTOJWhux/G00KTRnGUvDr6PHqdeXTulI+MhNaIRTSRN0FThJR0TyOwGoJ1Y2c66ZSwAnkWn3OhtZ7q87STXU2Fp27qP3MMrfukEfWsuq2cM/C8y+c6262dXdkoX37N5qo/dEQafyhY7wA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, #"Time(send)" = _t, #"Time(recive)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Time(send)", type datetime}, {"Time(recive)", type datetime}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Id"}, {{"all data", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
#"Expanded all data" = Table.ExpandTableColumn(#"Grouped Rows", "all data", {"Time(send)", "Time(recive)", "Index"}, {"all data.Time(send)", "all data.Time(recive)", "all data.Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded all data",{{"all data.Time(send)", "Time(send)"}, {"all data.Time(recive)", "Time(recive)"}, {"all data.Index", "Index"}})
in
#"Renamed Columns"
Result:
Apply it and create two measures:
previous recive time per ID = CALCULATE(SELECTEDVALUE(Table1[Time(recive)]),FILTER(ALLEXCEPT(Table1,Table1[Id]),Table1[Index]=MAX(Table1[Index])-1))
time diff = DATEDIFF(SELECTEDVALUE(Table1[Time(send)]),[previous recive time per ID],MINUTE)
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He