Find differences between rows

[KW123]

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
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 · Accepted solution]

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]

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]

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]

@FreemanZ 

I have been playing around with calculating the opening balance for each customer ID.  

I think what I would need is a variable calculation where we declare the opening balance (First $ amount for the first day the account was opened) and then whenever there is a next row where the $ is >, use the DAX you created for me. 

I hope that makes more sense. 

[KW123]

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.