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 

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.