Need Help On Getting Value From Previous Row

[NaudeJP]

Hi Everyone,

I am new to PowerBI and please need help on a formula.

I am building a PowerBI fuel consumption model for a road freight company. I need to add a new column after the "Odometer" column on the query below. The value in the new column needs to be the previous row's value in the "Odometer" column- if the current value in the "Vehicle" column matches the value of the previous row's "Vehicle" column. I hope this makes sense!

This needs to be done in order for us to calculate the distance driven by a specific vehicle since the previous fuel fill-up.

[edhans · Accepted solution]

Hi @NaudeJP 

Your original data shows you are in Power Query. This code sample will provide that data in Power Query before it loads to the data model.

You can see it returns the previous odometer reading unless it is the first odometer reading for that vehicle, and then it returns null.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUoEYkMjYxOlWJ1oJSOYgLGJGVjAGMhJAmIjmIAJVIURUA9YwBSqwsTEFKgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnitID = _t, Vehicle = _t, Odometer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UnitID", Int64.Type}, {"Odometer", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"Vehicle"}, {{"All Rows", each _, type table [UnitID=number, Vehicle=text, Odometer=number]}}
            ),
    #"Added Index" = Table.AddColumn(#"Grouped Rows", "Added Index", each Table.AddIndexColumn([All Rows], "Index", 0)),
    #"Added Prev Odometer" = 
        Table.AddColumn(
            #"Added Index", 
            "Added Prev Odometer", 
            each Table.AddColumn(
                [Added Index], 
                "Prev Odometer",
                let
                    varTable = [Added Index]
                in 
                each try varTable[Odometer]{[Index]-1} otherwise null
                )
            ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Prev Odometer",{"Added Prev Odometer"}),
    #"Expanded Added Prev Odometer" = Table.ExpandTableColumn(#"Removed Other Columns", "Added Prev Odometer", {"UnitID", "Vehicle", "Odometer", "Prev Odometer"}, {"UnitID", "Vehicle", "Odometer", "Prev Odometer"})
in
    #"Expanded Added Prev Odometer"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

A calculated column may work for your needs, but I generally try to avoid them if possible, but your question isn't a black and white issue as to where this goes. See which works best for you! In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

[harshnathani]

Hi @NaudeJP ,

You can have a look at this blog

https://community.powerbi.com/t5/Community-Blog/Getting-Previous-Values-in-Power-BI-Part-1/ba-p/1143...

Regards,

Harsh Nathani

[ryan_mayu]

@NaudeJP 

I can't see the screenshot you provided clearly.

Hope below example can help you.

perviousvalue = MAXX(FILTER('Table','Table'[order]=earlier('Table'[order])-1),'Table'[test])

[NaudeJP]

Thanks, it seems like it might work. I am just getting this error now:

How can I get this fixed?

Thanks again for the help!

[harshnathani]

Hi @NaudeJP ,

I think you are creating a measure. (Not sure though )

You need to create a Calculated Column.

https://www.sqlbi.com/articles/dax-error-messages-in-power-bi/

Regards,

Harsh Nathani