Forum Discussion

tespinal's avatar
tespinal
Frequent Visitor
9 years ago
Solved

Calculating Total Balances

Good afternoon,

 

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.  

 

CALCULATE(SUM( 'TABLE'[Balance]), LASTNONBLANK('TABLE'[Date], SUM(TABLE[Balance]))) 

 

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
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi tespinal,

    In the resource data, there are multiple transaction in one day. But the time is different, so you want to get the lasted transaction and sum them, right? If it is, I try to reproduce using the sample data.



    Then create a measure using the following formula.

    Result = CALCULATE(SUM(Table1[BALANCE]),FILTER(Table1,Table1[DATE]=CALCULATE(MAX(Table1[DATE]),ALLEXCEPT(Table1,Table1[ID]))))

    Create a card used to display the result, please see the following screenshot, it returns the expected result.

     

    Best Regards,
    Angelia

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi tespinal, are you writing this as a measure or a calculated column?

    • tespinal's avatar
      tespinal
      Frequent Visitor
      9 years ago

      I'm trying to write it as a measure so I can display it on a card visual. 

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        I think I managed to get it working using the following formula.

         

        Latest Balance = CALCULATE(SUM('Table'[BALANCE]),LASTNONBLANK('Table'[DATE],1))

         

        I used a Multi Row Card with just [ID] and [Latest Balance] in the fields.

         

        I also adjusted your data.  For [ID] 1 & 3 you had two values with the same date.  So I adjusted 1 to be earlier.

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    The records need a field that uniquely defines "latest".  I don't see that.  One cannot rely on the order that they appear - you could add I think a Key field column  as part of the Query Editor that will autonumber - - at least I think so I've never tried.....