Forum Discussion

vrocca's avatar
vrocca
Advocate 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
Advocate IV
8 years ago

Hey Diego - I spent some time looking for alternatives, but I'm not sure of a good way to arrive at what you are trying to do without creating a new column. The EARLIER function only works on columns, so the logic above wouldn't work for you. I'll see if I can test some other things and will let you know if I find something. I

 

Let me know if you find a workaround.

sirros_iot's avatar
sirros_iot
Helper III
8 years ago

vrocca, I am trying to do it:

.Tdiff = CALCULATE( VALUES(RealTimeData);
	FILTER(ALL(RealTimeData);

	VAR Previoustime =
	 CALCULATE(VALUES(RealTimeData[_ts]);
		 FILTER(ALL(RealTimeData);
		 RealTimeData[id] = EARLIER(RealTimeData[id])-1
		 ))
	
RETURN
         (RealTimeData[_ts] - Previoustime)))

[_ts] = Equal to timestamp value: like this (9428293029)

 

I think that with this measure I could have the time in seconds.