cancel
Showing results for
Did you mean:
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
Community Champion

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

```MyMeasure =
SUMX (
SUMMARIZE (
'Balance',
'Balance'[created_at],
'Balance'[account_id]
),
CALCULATE (
VAR maxid =
MAX ( 'Balance'[id] )
RETURN
CALCULATE ( SUM ( 'Balance'[balance] ), 'Balance'[id] = maxid )
)
)```
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()))

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

Super User

@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
Solution Sage

Hi @kovan ,

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

Kind regards,

Rohit

Solution Sage

Hi @kovan ,

Please create a calculated column as shown below :

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

Kind regards,

Rohit

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.

``````Last balance measure: =
SUMX (
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.

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

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?

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

Community Champion

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

this calculation is giving back Maximum balance for each id

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] )``````