Forum Discussion

KW123's avatar
KW123
Helper V
3 years ago
Solved

Find differences between rows

Hi,  I have a data set:  Date Customer ID Transaction $ What I need to calculate 01/01/2023     1 $0.00 $0.00 01/01/2023     2 $0.00 $0.00 01/01/2023     3 $100 $100.00 ...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi KW123 

    try like:

    Diff = 
    VAR _customer = [CustomerID]
    VAR _date = [Date]
    VAR _table =
    FILTER(
        TableName,
        TableName[CustomerID] = _customer
            &&TableName[Date] < _date
        )
    )
    VAR _lastdate =
    MAXX( _table, TableName[Date])
    VAR _lasttransaction =
    MAXX(
        FILTER(
             _table,
             TableName[Date]=_lastdate
        ),
       TableName[Transaction]
    )
    RETURN
    [Transaction] - _lasttransaction
FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi KW123 

try like:

Diff = 
VAR _customer = [CustomerID]
VAR _date = [Date]
VAR _table =
FILTER(
    TableName,
    TableName[CustomerID] = _customer
        &&TableName[Date] < _date
    )
)
VAR _lastdate =
MAXX( _table, TableName[Date])
VAR _lasttransaction =
MAXX(
    FILTER(
         _table,
         TableName[Date]=_lastdate
    ),
   TableName[Transaction]
)
RETURN
[Transaction] - _lasttransaction
  • KW123's avatar
    KW123
    Helper V
    3 years ago

    Hi FreemanZ 

    Thank you so much for this Dax.  It is exactly what I needed.  

    Would you be able to help me take it a step further? I have realized that I need to adjust it slightly.  I believe I need some sort of an IF statement.  I'd like the DAX to return the first balance for each customer.  If the balance for the next day is greater than today's date, then insert the DAX you sent to me.  

    For example: 

    Date  Customer ID  Transaction $
    01/01/2023    1$0.00
    01/01/2023    2$100.00
    01/01/2023    3$300.00
    01/02/2023    1$100.00
    01/02/2023    2$100.00
    01/02/2023    3$400.00
    01/03/2023    1$100.00
    01/03/2023    2$200.00
    01/03/2023    3$400.00


    For Customer 1 It would look like this:

    Date  Customer ID  Transaction$  How I need it calculated
    01/01/2023    1$0.00$0.00 (Return this number from Data)
    01/02/2023    1$100.00$100.00 (DAX to find difference since this number is > than Prev Day)
    01/03/2023    1$100.00$0.00 (No difference between prev day and today)


    Customer 2 

    Date  Customer ID  Transaction$  How I need it calculated
    01/01/2023    2$100.00$100.00 (Return starting number)
    01/02/2023    2$100.00$0.00 (No difference between days)
    01/03/2023    2$200.00$100.00 (DAX to find difference since this day > prev day)


    Customer ID 3 

    Date  Customer ID  Transaction$  How I need it calculated
    01/01/2023    3$300.00$300.00 (Return this number)
    01/02/2023    3$400.00$100.00 (DAX to find difference)
    01/03/2023    3$400.00$0.00 (No difference)


    I hope that makes sense! Unfortunately not all accounts will begin on the first of the month either.  So it will be whichever is the opening balance at any given time. 

    Thanks again for your help, I really appreciate it. 

    • FreemanZ's avatar
      FreemanZ
      Super User
      3 years ago

      hi KW123 

      not sure about " I'd like the DAX to return the first balance for each customer. If the balance for the next day is greater than today's date, then insert the DAX you sent to me. ", even i compare with your data table. Could  you elaborate the logic behind?

      • KW123's avatar
        KW123
        Helper V
        3 years ago

        Hi FreemanZ 

        Yes happy to explain further! It is a bit complicated how they have the data set up in the data base.  Instead of each row showing what the transaction amount is, it shows it as a cummulative total.  The actual transaction amount is for the difference between the rows. With the exception of the opening balance.  The opening balance on whichever the day they opened the account is, is the actual transaction amount.  Anything after that, it will be the difference between rows.  If the next row is the same as the current row transaction, that means the customer did not have a transaction (or $0) if the number is >, then whichever the difference is between the numbers is what the customer actually did in transaction amount. 

        A customer can open the account and not make a transaction. Their opening balance would be 0.  But if they opened their account and right away made a transaction, it would be for whichever is the actual amount listed in the row. 

        I hope that makes a bit more sense.