Calculating Remaining Value

[Anonymous]

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.

[amitchandak]

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/823601

[Anonymous]

@amitchandak 

I 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?