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

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

tespinal's avatar
tespinal
Frequent Visitor
9 years ago

Thanks for the feedback everyone. We were able to come up with the solution using SQL query direct from the database. I appreciate all the help.