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....

NeilAm12's avatar
NeilAm12
Icon for Helper I rankHelper I
7 years ago
vrocca wrote:

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 and they work for the most part, but my issue is that I have some bad data where the odometer readings are incorrect and therefore not incremental (i.e. for 4/1/17 it may read 23,500  and then for 4/5/17 it may read 22,500). 

 

The formula I've been working on looks like this:

 

Previous Odometer := CALCULATE(MAX(Transactions[Odometer #]),ALL(Transactions),Transactions[Vehicle #] = EARLIER(Transactions[Vehicle #]),Transactions[Transaction Date] < EARLIER(Transactions[Transaction Date])  )

 

 

What happens in the scenario where the odometer has a bad reading is that it keeps using that value for all future readings, and throws off the calculations from that point forward.

 

Example:

 

Transaction Date          Odometer#          Previous Odometer

02/08/17                      66770                   66320

02/15/17                      97315 (bad data)  66770                              

02/20/17                      67630                   97315

02/27/17                      68056                   97315   <-- here is my issue. this should read 67630. Value keeps repeating for all rows below after this

03/04/17                      68483                   97315

 

 

Any suggestions on how I need to modify my DAX formula so that I don't get the same value being repeated?


Hello All,

 

I think my issue is the same with this but with a column with different data.

I want my RRA column to get the previous data of Data-Xi column based on Concatenate column data and blank the 1st data.

I hanve date field and Combine column of Mini Company and Machine.

i try different formula but error will show.

 

Below is my code:

RRA = (CALCULATE(MAX(Test[Data-Xi]),(DB[Concatenate]),FILTER(DB,DB[Entry Date/Time]<DB[Entry Date/Time])))