Forum Discussion
Compare value between multiple records
- 7 years ago
Then all you should have to do is replace the blue text in the query above with ROW-ID instead of [Datetime]
So you have a Customer_ID which identifies unique customers, which is great. What v-yuta-msft is trying to ask is whether you have some ID or index that can be used to determine what order the records for a specific customer would be.
If I ran this query to create a calculated table, how would I know which row was 1st, then 2nd, etc? Is there an index or other column that indicates the order of records?
TempTable = FILTER(ALL(Table1), [Customer_ID] = "123ABC")
This can be done by date, but it's much easier with an index. You would want something like this as a calculated column when using date:
QtyUpDown =
VAR PrevRecordID = CALCULATE( MAX(Table1[Record_ID]), FILTER(Table1, Table1[Customer_ID] = EARLIER(Table1[Customer_ID] && Table1[DateTime]<EARLIER(Table1[DateTime])))
RETURN
IF ( ISBLANK(PrevRecordID), "No previous record",
SWTICH( TRUE(),
Table1[QtyBought] < CALCULATE(SELECTEDVALUE(Table1[QtyBought]), FILTER(ALL(Table1), Table1[Record_ID] = PrevRecordID)), "QTY Went Down",
Table1[QtyBought] > CALCULATE(SELECTEDVALUE(Table1[QtyBought]), FILTER(ALL(Table1), Table1[Record_ID] = PrevRecordID)), "QTY Went Up",
Table1[QtyBought] = CALCULATE(SELECTEDVALUE(Table1[QtyBought]), FILTER(ALL(Table1), Table1[Record_ID] = PrevRecordID)), "QTY Stayed Same",
"Some error occured" )
)
Replace the section in blue with however you want to determine the order of records. Note that this will give unintentional results in the case that a customer has two records with the same [DateTime] field - it will calculate them both vs the QTYBought before both.
Cmcmahan v-yuta-msft I can create a ROW-ID in the Database as a sequence.
- Cmcmahan7 years agoResident Rockstar
Then all you should have to do is replace the blue text in the query above with ROW-ID instead of [Datetime]