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