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

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

Hello vrocca

 

I need to find the previous record to get the difference between the time. 

My problem is that I can't use calculated columns, I only can use measures to solve this problem.

 

Can I find out the solution only with measures?

 

thanks,

Diego Schneiders Lutckmeier

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.

  • 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