Calculating Total Balances

[kovan]

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.

[tamerj1 · Accepted solution]

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

@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()))

[kovan]

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

[Tahreem24]

@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()))

[rohit_singh]

Hi @kovan ,

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

Kind regards,

Rohit

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

[rohit_singh]

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

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

[Jihwan_Kim]

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 (
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[ID], Data[Date] ),
        "@lowestamount", CALCULATE ( MIN ( Data[Balance] ) )
    ),
    [@lowestamount]
)

[kovan]

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]

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]

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]

@kovan 

Please use

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

[kovan]

this calculation is giving back Maximum balance for each id 

[tamerj1]

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