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

I'm trying to do this:

 

.test =
VAR X =
CALCULATE(SUM(RealTimeData[_ts]);
FILTER(ALL(RealTimeData);

SUMX(
FILTER(RealTimeData;RealTimeData[_ts] >= EARLIER(RealTimeData[_ts]));
RealTimeData[_ts])))

// VAR UnixDays = X / (60*60*24)
// VAR Times = ((DATEVALUE("1/1/1970") + UnixDays))

RETURN
// DATEDIFF(MIN(RealTimeData[tempo]);Times;SECOND)
X

 

This is my data: I have timestamp transformed too.

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

    What are you trying to return? What is it that you are trying to calculate?

    • sirros_iot's avatar
      sirros_iot
      Icon for Helper III rankHelper III
      8 years ago
      Sorry kkk, I'm trying to get the difference between the times in the table. It will be nescesary to know if one machine is working in the time or not. Let's see, I have the average time from the each ID, and I need to compare to the time difference between the rows.

      Like this,
      IF ( [time_difference] > 2 * [average_time]; "stopped"; "working")

      Thanks for the reply
      • vrocca's avatar
        vrocca
        Icon for Advocate IV rankAdvocate 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.