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

Calculating Total Balances

hi community

 

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.  

 

 

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
tamerj1
Community Champion
Community Champion

@kovan 

Please use the following measure

MyMeasure =
SUMX (
    SUMMARIZE (
        'Balance',
        'Balance'[created_at],
        'Balance'[account_id]
    ),
    CALCULATE (
        VAR maxid =
            MAX ( 'Balance'[id] )
        RETURN
            CALCULATE ( SUM ( 'Balance'[balance] ), 'Balance'[id] = maxid )
    )
)

View solution in original post

13 REPLIES 13
tamerj1
Community Champion
Community Champion

@kovan 

Please use the following measure

MyMeasure =
SUMX (
    SUMMARIZE (
        'Balance',
        'Balance'[created_at],
        'Balance'[account_id]
    ),
    CALCULATE (
        VAR maxid =
            MAX ( 'Balance'[id] )
        RETURN
            CALCULATE ( SUM ( 'Balance'[balance] ), 'Balance'[id] = maxid )
    )
)
Tahreem24
Super User
Super User

@kovan Try this calculated column:

Column =
VAR Date_ = CALCULATE(MAX(BalanceTable[Date]),ALLEXCEPT(BalanceTable,BalanceTable[ID]))
VAR sum_ = CALCULATE(SUM(BalanceTable[Balance]),FILTER(ALLEXCEPT(BalanceTable,BalanceTable[ID]),BalanceTable[Date]=Date_))
RETURN (IF(BalanceTable[Date]=Date_,sum_,BLANK()))
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis
kovan
Frequent Visitor

this is so good but i have one issue it is SUMing all balance on a same Date/and time 
but now i want to SUM when it has Maximum "id" and latest date

@kovan So tweak my dax like below:

Column =
VAR Date_ = CALCULATE(MAX(BalanceTable[Date]),ALLEXCEPT(BalanceTable,BalanceTable[ID]))
VAR sum_ = CALCULATE(SUM(BalanceTable[Balance]),FILTER(ALLEXCEPT(BalanceTable,BalanceTable[ID]),BalanceTable[Date]=Date_ || BalanceTable[ID]=MAX(BalanceTable[ID])))
RETURN (IF(BalanceTable[Date]=Date_,sum_,BLANK()))
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , and Fortune 500 Companies Analysis

Hi @kovan ,

Try my solution below I've fixed the calculation now in my previous comment :

rohit_singh_0-1653565696205.png

rohit_singh_1-1653565712835.png

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

rohit_singh
Solution Sage
Solution Sage

Hi @kovan ,

Please create a calculated column as shown below :

rohit_singh_0-1653556724373.png

Last Balance =

var _maxdate =
CALCULATE(
max(Balances[date]),
allexcept(Balances, Balances[ID])
)

var _maxbal =
CALCULATE(
max(Balances[BALANCE]),
FILTER(allexcept(Balances, Balances[ID]&& Balances[BALANCE], Balances[DATE] = _maxdate)
)

Return
if(Balances[DATE] = _maxdate, _maxbal, blank())
 
This is the result

rohit_singh_1-1653556817967.png

 

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

Jihwan_Kim
Super User
Super User

Hi,

I assume, on the same date for the same ID - the lowest amount is the latest value. -> Am I correct?

Or, how do you define what is the latest value on the same date?

 

If my assumption is correct, please check the below picture and the attached pbix file.

 

Untitled.png

 

 

Last balance measure: =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[ID], Data[Date] ),
        "@lowestamount", CALCULATE ( MIN ( Data[Balance] ) )
    ),
    [@lowestamount]
)

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


kovan
Frequent Visitor

the latest Balance based on date/time sorry for not mentioning 

ID               DATE                                BALANCE

 

1                 2/13/17 01:22:00 AM         500.00

1                 2/13/17 02:30:00 AM          450.00

2                 2/09/17  05:30:00 AM        100.00

3                 2/13/17   07:30:00 AM       700.00

3                 2/13/17   09:30:00 AM       300.00

4                 2/10/17   08:30:00 AM      1500.00

tamerj1
Community Champion
Community Champion

Hi @kovan 
But both blances at the same date. What do yo mean then by last date? Or the date is actually date/time column?

kovan
Frequent Visitor

yes the date is actually  date/time

 

ID               DATE                                BALANCE

 

1                 2/13/17 01:22:00 AM         500.00

1                 2/13/17 02:30:00 AM          450.00

 

tamerj1
Community Champion
Community Champion

@kovan 

Please use

Last balance =
CALCULATE (
    MAX ( TableName[BALANCE] ),
    TableName[Date] = MAX ( TableName[Date] )
)
kovan
Frequent Visitor

this calculation is giving back Maximum balance for each id 

 

tamerj1
Community Champion
Community Champion

@kovan 
It shouldn't. Please make sure you are creatin a measure not a calculated column. To be more safe please use

Last balance =
VAR MaxDate =
    MAX ( TableName[Date] )
RETURN
    CALCULATE ( SELECTEDVALUE ( TableName[BALANCE] ), TableName[Date] = MaxDate )

If you want to create a calculated column then

Last balance =
VAR IdTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR MaxDate =
    MAXX ( IdTable, TableName[Date] )
RETURN
    MAXX ( FILTER ( TableName, TableName[Date] = MaxDate ), TableName[BALANCE] )

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors