Forum Discussion
Calculating Remaining Value
New to Power BI so this may be either simple or not possible. But looking for suggestions.
I have two tables
Table 1
| ID | Spent |
| A | 100 |
| B | 80 |
| C | 30 |
Table 2
| ID | Date | Recieved |
| A | 12/5 | 75 |
| A | 12/4 | 30 |
| B | 12/3 | 100 |
| A | 12/1 | 20 |
| C | 11/20 | 10 |
I would like to add a column to table 2 that would be the amount of recieved money remaining (recieved - spent). If the value is negative, it would carry over to the more recent row. If there is no more recent row and the value is negative show the negative value. If there is no spent money then just subtract 0.
So the table would be something like this.
| ID | Date | Recieved | Reamining |
| A | 12/5 | 75 | ( -50 carry over from 12/4 + 75) = 25 |
| A | 12/4 | 30 | ( -80 carry over from 12/1 + 30) = -50 but show as 0 |
| B | 12/3 | 100 | (80-100) = 20 |
| A | 12/1 | 20 | (20-100) = -80 but show as 0 |
| C | 11/20 | 10 | (10-30) = -20 show as -20 because no more recent entries |
This is more complicated than I can google to figure out on my own.
Thanks in advance.
2 Replies
Take the cumulative of table 2 and then try the diff
CALCULATE(sum(table2[received]),filter(table2,table2[received]<=max(table2[ Date])))Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601I am not sure that is what I am looking for. Maybe this is what you are suggesting, but what I am thinking now is
Create a column in table 2 that is the total receieved to date. That being a matching ID and date <= date of the row. Then I can take the difference from table 1.
Can you help with creating that new column sums the recieved of rows with matching the IDs and has a date <= date of the row?
