Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate row difference based on category / index.

Hi All,

 

I'm confronted with a nice puzzle in Power BI which I can't seem to solve.

Hoping you can help me to get into the right direction :) 

 

The 'functional' question is easy. Say, there are locations and on all locations, trucks drive around (of course not every day). The trucks register the hours they have driven and I would like to calculate the hours they have driven per day (calculated based on the value of the previous day). The source data looks like this:

 

DateLocationVehicle TagCounter
1-4-2018AmsterdamAB123110
2-4-2018AmsterdamAB123118
4-4-2018AmsterdamAB123126
1-4-2018RotterdamRC234500
2-4-2018RotterdamRC234508
3-4-2018RotterdamRC234510

 

Since the trucks belong to different locations, and don't drive around every day, I started with creating an index based on a unique key (Location & Vehicle Tag). Thanks to some posts by Eric_Zhang I've managed to do so, and now the data looks like this.

 

DateLocationVehicle TagCounterKeyIndex
1-4-2018AmsterdamAB123110AmsterdamAB1231
2-4-2018AmsterdamAB123118AmsterdamAB1232
4-4-2018AmsterdamAB123126AmsterdamAB1233
1-4-2018RotterdamRC234500RotterdamRC2341
2-4-2018RotterdamRC234508RotterdamRC2342
3-4-2018RotterdamRC234510RotterdamRC2343

 

However, now comes the tricky part (at least for me): calculating the difference between the values (based on the combination of the Key and index). I've been puzzling with the logic described by Zubair_Muhammad in this post, or the suggestion from ImkeF described in this post. However, I simply cannot sort out how to incorporate the 'key' into the calculation.

 

Ultimately, the result would look like this:

 

DateLocationVehicle TagCounterKeyIndexResult
1-4-2018AmsterdamAB123110AmsterdamAB1231110
2-4-2018AmsterdamAB123118AmsterdamAB12328
4-4-2018AmsterdamAB123126AmsterdamAB12338
1-4-2018RotterdamRC234500RotterdamRC2341500
2-4-2018RotterdamRC234508RotterdamRC23428
3-4-2018RotterdamRC234510RotterdamRC23432

 

As said, any suggestion / help would be much appreciated! Thanks in advance!

Need Help
  • ImkeF's avatar
    ImkeF
    8 years ago

    Please copy and paste this code into the advanced editor and follow the steps. For performance reasons, the creation of the nested index and the fetching of the previoius row is included in one function ("MyFunction"), which is a bit of an advanced techique:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNTDRNbRQ0lFyzC0uSS1KScwFsZ0MjYyBtKGhgVKsDlCZEQFlFhBlJviVGZlBlCEsDcovgSsLcjYyNgHSpgYYlmJXBrXUGL8ykBdiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Location = _t, #"Vehicle Tag" = _t, Counter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Location", type text}, {"Vehicle Tag", type text}, {"Counter", Int64.Type}}),
    WithKey = Table.AddColumn(#"Changed Type", "Key", each Text.Combine({[Location], [Vehicle Tag]}, ""), type text),
    MyFunction = (SourceTable as table) =>
        let
            AddIndex = Table.AddIndexColumn(SourceTable , "Index", 0, 1),
            DiffToPrevious = Table.AddColumn(AddIndex, "DiffToPrevious", each if [Index]=0 then [Counter] else [Counter]-AddIndex{[Index]-1}[Counter])
        in
            DiffToPrevious,
    Next = WithKey,
    #"Grouped Rows" = Table.Group(Next, {"Key"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ExecuteFunction", each MyFunction([All])),
    #"Expanded ExecuteFunction" = Table.ExpandTableColumn(#"Added Custom", "ExecuteFunction", {"Date", "Location", "Vehicle Tag", "Counter", "Index", "DiffToPrevious"}, {"Date", "Location", "Vehicle Tag", "Counter", "Index", "DiffToPrevious"})
in    
    #"Expanded ExecuteFunction"

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Please copy and paste this code into the advanced editor and follow the steps. For performance reasons, the creation of the nested index and the fetching of the previoius row is included in one function ("MyFunction"), which is a bit of an advanced techique:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNTDRNbRQ0lFyzC0uSS1KScwFsZ0MjYyBtKGhgVKsDlCZEQFlFhBlJviVGZlBlCEsDcovgSsLcjYyNgHSpgYYlmJXBrXUGL8ykBdiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Location = _t, #"Vehicle Tag" = _t, Counter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Location", type text}, {"Vehicle Tag", type text}, {"Counter", Int64.Type}}),
    WithKey = Table.AddColumn(#"Changed Type", "Key", each Text.Combine({[Location], [Vehicle Tag]}, ""), type text),
    MyFunction = (SourceTable as table) =>
        let
            AddIndex = Table.AddIndexColumn(SourceTable , "Index", 0, 1),
            DiffToPrevious = Table.AddColumn(AddIndex, "DiffToPrevious", each if [Index]=0 then [Counter] else [Counter]-AddIndex{[Index]-1}[Counter])
        in
            DiffToPrevious,
    Next = WithKey,
    #"Grouped Rows" = Table.Group(Next, {"Key"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ExecuteFunction", each MyFunction([All])),
    #"Expanded ExecuteFunction" = Table.ExpandTableColumn(#"Added Custom", "ExecuteFunction", {"Date", "Location", "Vehicle Tag", "Counter", "Index", "DiffToPrevious"}, {"Date", "Location", "Vehicle Tag", "Counter", "Index", "DiffToPrevious"})
in    
    #"Expanded ExecuteFunction"
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Imke,

       

      Just tried your proposed solution, and I got it working (more or less). Will spend a large amount of my day tomorrow re-reading / producing the solution steps. Awesome bit of code!!! Many many thanks!!!!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

    This can be solved with a calculated column as well (without creating a key column).

     

    =if(ISBLANK(LOOKUPVALUE(Data[Counter],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Location]=EARLIER(Data[Location])&&Data[Vehicle Tag]=EARLIER(Data[Vehicle Tag])&&Data[Date]<EARLIER(Data[Date]))),[Location],[Location],[Vehicle Tag],[Vehicle Tag])),[Counter],[Counter]-LOOKUPVALUE(Data[Counter],Data[Date],CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Location]=EARLIER(Data[Location])&&Data[Vehicle Tag]=EARLIER(Data[Vehicle Tag])&&Data[Date]<EARLIER(Data[Date]))),[Location],[Location],[Vehicle Tag],[Vehicle Tag]))