Forum Discussion

kovan's avatar
kovan
Frequent Visitor
4 years ago
Solved

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.

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

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

13 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      kovan
      Frequent Visitor
      4 years ago

      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's avatar
        tamerj1
        Community Champion
        4 years ago

        kovan 

        Please use

        Last balance =
CALCULATE (
    MAX ( TableName[BALANCE] ),
    TableName[Date] = MAX ( TableName[Date] )
)
  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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's avatar
      kovan
      Frequent Visitor
      4 years ago

      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

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

    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's avatar
      kovan
      Frequent Visitor
      4 years ago

      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

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

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