Forum Discussion

vrocca's avatar
vrocca
Icon for Advocate IV rankAdvocate IV
9 years ago
Solved

Reference previous row to get value

Hi all,   I have a dataset with Vehicles, Dates, and Odometer. I'm trying to create a calculation that returns the prior odometer reading for each row. I have followed several examples on the web a...
Need Help
  • vrocca's avatar
    vrocca
    9 years ago

    I was able to get something to work by creating a Row ID using RANKX, and then another column (Prior Row ID) using EARLIER to get the previous Row ID. I then added this to my original Previous Odometer calculation as an additional filter:

     

    Row ID := RANKX( ALL(Transactions[Transaction Date]), T ransactions[Transaction Date], , ASC, Dense)

     

    Previous Row ID := CALCULATE

                                          (  

                                           MAX(Transactions[Row ID]),

                                           ALL(Transactions),Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),

                                           Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date])

                                          )

     

    Previous Odoometer =
    CALCULATE(

                        MAX(Transactions[Odometer #]),

                        ALL(Transactions),

                        Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),

                        Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date]),

                        Transactions[Row ID] = EARLIER(Transactions[Previous Row ID]) )
    )

     

    Curious to see if there is a much simpler way to achieve the same results....

vrocca's avatar
vrocca
Icon for Advocate IV rankAdvocate IV
8 years ago

Hey Diego  - what is the reason as to why you are not able to create a calculated column? Are you connecting to a live cube? Could you create the calculated columns in the source?

sirros_iot's avatar
sirros_iot
Icon for Helper III rankHelper III
8 years ago

Yes, I'm connected to the live cube(Power BI online?), my data is coming from a device( arduino) and I'm not sure about the calculated columns. I tried to create but I didn't get anything from the power BI online. Thank you for the reply.