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 nu...
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

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.

    • tespinal's avatar
      tespinal
      Frequent Visitor
      9 years ago

      I appreciate the feedback, but the results from the formula same as the ones I got from my original formula. The balances may have the same date because they can change throughout the day, like a checking account. It's like if I have 3 checking accounts and I do multiple transactions throughout the day, I am looking to get the sum of all 3 account balances as of right now. 

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        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