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

      this calculation is giving back Maximum balance for each id 

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

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