Forum Discussion

sabeensp's avatar
sabeensp
Helper IV
7 years ago
Solved

Compare value between multiple records

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.

Need Help
  • Cmcmahan's avatar
    Cmcmahan
    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]

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    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.

     

      • Cmcmahan's avatar
        Cmcmahan
        Resident Rockstar
        7 years ago

        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.