Forum Discussion

joaocarlos95's avatar
joaocarlos95
Frequent Visitor
8 years ago
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...
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    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

joaocarlos95's avatar
joaocarlos95
Frequent Visitor
8 years ago

Thank you for answering Greg, I don't know if you're a bot :-).

 

I was trying to use EARLIER, but I'm having a difficult time working with row contex, for example, I tried this: 

 

Measure = SUMX(FILTER(dProcessos;dProcessos[ID]=EARLIER(dProcessos[ID]));dProcessos[data_envio_origem]-EARLIER(dProcessos[data_recebimento_destino])

 

But I get that error in the second "EARLIER" that says that EARLIER makes reference to a row contenx that doesn't exist. I'm gonna read your article right now to see if I can finally understand EARLIER.

 

BTW, since you're already here :-), do you know why my file increases to 20GB when I use that M script? That script worked so bautifuly that I wanted to use it, but it takes 4 hours to apply my changes.