cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tespinal
Frequent Visitor

Calculating Total Balances

Good afternoon,

 

I am working on a project where I am trying to add the total balances of id numbers. In order to get this, I need to pull the last balance posted from our files from several id numbers and add them together. I spent a great deal of time researching and came across a couple of ideas, like using LastNonBlank or date, but they aren't working. Here is some context using some dummy numbers.

 

 

ID               DATE            BALANCE

 

1                 2/13/17          500.00

1                 2/13/17          450.00

2                 2/09/17          100.00

3                 2/13/17          700.00

3                 2/13/17          300.00

4                 2/10/17         1500.00

 

So what I am trying to accomplish is to write a formula telling PBI to take the most recent balance for each ID and add them together (i.e. (ID 1) 450 + (ID2)  100 + (ID 3)  300 + (ID 4) 1500.00. I've listed the formula I've come up with below.  

 

CALCULATE(SUM( 'TABLE'[Balance]), LASTNONBLANK('TABLE'[Date], SUM(TABLE[Balance]))) 

 

When I tried this, it appeared to add up all the numbers and not just the specific ones I am triyng to pull. Any help would be greatly appreciated.

1 ACCEPTED SOLUTION

Hi @tespinal,

In the resource data, there are multiple transaction in one day. But the time is different, so you want to get the lasted transaction and sum them, right? If it is, I try to reproduce using the sample data.

1.PNG

Then create a measure using the following formula.

Result = CALCULATE(SUM(Table1[BALANCE]),FILTER(Table1,Table1[DATE]=CALCULATE(MAX(Table1[DATE]),ALLEXCEPT(Table1,Table1[ID]))))

Create a card used to display the result, please see the following screenshot, it returns the expected result.

2.PNG

 

Best Regards,
Angelia

View solution in original post

8 REPLIES 8
CahabaData
Memorable Member
Memorable Member

The records need a field that uniquely defines "latest".  I don't see that.  One cannot rely on the order that they appear - you could add I think a Key field column  as part of the Query Editor that will autonumber - - at least I think so I've never tried.....

www.CahabaData.com
Anonymous
Not applicable

Hi @tespinal, are you writing this as a measure or a calculated column?

I'm trying to write it as a measure so I can display it on a card visual. 

I think I managed to get it working using the following formula.

 

Latest Balance = CALCULATE(SUM('Table'[BALANCE]),LASTNONBLANK('Table'[DATE],1))

 

I used a Multi Row Card with just [ID] and [Latest Balance] in the fields.

 

I also adjusted your data.  For [ID] 1 & 3 you had two values with the same date.  So I adjusted 1 to be earlier.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

I appreciate the feedback, but the results from the formula same as the ones I got from my original formula. The balances may have the same date because they can change throughout the day, like a checking account. It's like if I have 3 checking accounts and I do multiple transactions throughout the day, I am looking to get the sum of all 3 account balances as of right now. 

Hi @tespinal,

In the resource data, there are multiple transaction in one day. But the time is different, so you want to get the lasted transaction and sum them, right? If it is, I try to reproduce using the sample data.

1.PNG

Then create a measure using the following formula.

Result = CALCULATE(SUM(Table1[BALANCE]),FILTER(Table1,Table1[DATE]=CALCULATE(MAX(Table1[DATE]),ALLEXCEPT(Table1,Table1[ID]))))

Create a card used to display the result, please see the following screenshot, it returns the expected result.

2.PNG

 

Best Regards,
Angelia

Thanks for the feedback everyone. We were able to come up with the solution using SQL query direct from the database. I appreciate all the help. 

Hi @tespinal,

I am very gald to hear you have resolve your issue, please mark the corresponding reply as answer if it's helpful, so other people will find solution easily.

If you have other issues, please let me know.

Best Regards,
Angelia

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.