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 numbe...
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 )
    )
)
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! 🙂

  • Tahreem24's avatar
    Tahreem24
    Super User
    4 years ago

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