Compare value between multiple records

[sabeensp]

Hello,

I'm struggling with the following logic.

In my table, I have DateTime, Customer_ID, CustomerName, QtyBought and so on.

I need to implement following logic.

If Record # 2 of Same Customer has QtyBought > QtyBought in Record#1 then Show "QTY Went Up".

If Record # 2 of Same Customer has QtyBought < QtyBought in Record#1 then Show "QTY Went Down".

of course one customer can have many records. So each record needs to be compared with previous record. Each Record has DateTime.

Apprecite your help.

[v-yuta-msft]

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

[sabeensp]

@v-yuta-msftRow 1 or Row 2 or Row 3 = Record_ID (identifies each customer uniquely).

[Cmcmahan]

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.

[sabeensp]

@Cmcmahan  @v-yuta-msft I can create a ROW-ID in the Database as a sequence.

[Cmcmahan · Accepted solution]

Then all you should have to do is replace the blue text in the query above with ROW-ID instead of [Datetime]