Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KW123
Helper V
Helper V

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
1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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

View solution in original post

5 REPLIES 5
FreemanZ
Super User
Super User

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

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. 

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?

@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. 

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. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.