Forum Discussion
Compare value between multiple records
Then all you should have to do is replace the blue text in the query above with ROW-ID instead of [Datetime]
sabeensp ,
So Record #1 and Record #2 means row number, right? If it is, you need to add an index column so that we can compare current row with previous row. Then create a calculate column using DAX below:
Result =
VAR Current_Index = Table[Index]
VAR Previous_Index = Current_Index - 1
VAR Current_QtyBought =
CALCULATE (
MAX ( Table[QtyBought] ),
FILTER ( Table, Table[Index] = Current_Index )
)
VAR Previous_QtyBought =
CALCULATE (
MAX ( Table[QtyBought] ),
FILTER ( Table, Table[Index] = Previous_Index )
)
RETURN
IF (
Current_QtyBought > Previous_QtyBought,
"QTY Went Up",
IF ( Current_QtyBought < Previous_QtyBought, "QTY Went Down" )
)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuta-msftRow 1 or Row 2 or Row 3 = Record_ID (identifies each customer uniquely).
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.
Then all you should have to do is replace the blue text in the query above with ROW-ID instead of [Datetime]