Forum Discussion

KW123's avatar
KW123
Helper V
3 years ago
Solved

Find differences between rows

Hi, 

I have a data set: 

DateCustomer IDTransaction $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
01/01/2023    4$200$0.00
01/02/2023    1    $0.00$0.00
01/02/2023    2    $50.00$50.00
01/02/2023    3$100.00$0.00
01/02/2023    4

$250.00

$50.00

01/03/2023    1$0.00$0.00
01/03/2023    2$50.00$0.00
01/03/2023    3$100.00$0.00
01/03/2023    4$600.00$350.00
01/04/2023    1$100.00$100.00
01/04/2023    2$50.00$0.00
01/04/2023    3$150.00$50.00
01/04/2023    4$650.00$50.00
01/05/2023    1$100.00$0.00
01/05/2023    2$100.00$50.00
01/05/2023    3$200.00$150.00
01/05/2023    4$650.00$0.00
01/06/2023    1$100.00$0.00
01/06/2023    2$150.00$50.00
01/06/2023    3$300.00$100.00
01/06/2023    4$750.00$100.00
01/07/2023    1$300.00$200.00
01/07/2023    2$150.00$0.00
01/07/2023    3$300.00$0.00
01/07/2023    4$950.00$200.00


It is ordered by date.  Each customer ID has a transaction $ amount.  I need a DAX which will calculate the difference between each row by Customer ID, so not the row directly below it.  If there is no change, it should be $0.00  I just need to show when there is a transaction change, and what the difference is in that change from their previous transaction. 

I have tried the following two DAX but I get not enough memory both times:

Difference =
[Disb amount]-LOOKUPVALUE('Disb'[DisbAmt],Disb[CustomerID],[CustomerID],Disb[DisbDate],CALCULATE(MAX(Disb[DisbDate]), FILTER(Disb, Disb[CustomerID]=EARLIER(Disb[CustomerID])&&Disb[DisbDate]<EARLIER(Disb[DisbDate]))))

And

Colum difference 2 =
var baseFilter = FILTER('Disb','Disb'[CustomerID] = EARLIER('Disb'[CustomerID]))
var selectDate = CALCULATE(LASTDATE('Dates'[Date]),baseFilter,
FILTER(baseFilter, 'Disb'[DisbDate] < EARLIER('Disb'[DisbDate])))
return
'Disb'[Disb amount]- CALCULATE(sum('Disb'[DisbAmt]),baseFilter,
FILTER(baseFilter, 'Disb'[DisbDate] =selectDate))

And then I tried this one, but it doesn't take the CustomerID into consideration.  I do have two index columns, one which starts at 1 and the other at 0.

Column difference =
VAR Diff = Disb[Disb amount] - CALCULATE(SUM('Disb'[DisbAmt]),  FILTER ('Disb','Disb'[Index1] = EARLIER('Disb'[Index])))
RETURN IF(Diff = VALUE('Disb'[Disb amount]), 0, Diff)

Any help would be appreciated! Thank you
  • 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

5 Replies

  • 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?