Forum Discussion
Reference previous row to get value
- 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....
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....
https://community.powerbi.com/t5/Desktop/reference-Previous-row-using-measures/m-p/725894#M350298
help me out on this