DAX measure or calculated column for dynamical calculation of value based on date and ID

Question

This is a Power BI question. I would like a solution with either a DAX measure or calculated column. I have a table:

ID	Value	Date 1	Date 2	Value 2	Result
A	1000	08/12/2024	08/12/2024	-100	900
B	1200	08/12/2024	08/12/2024	-250	950
C	1500	08/12/2024	08/12/2024	0	1500
A	1000	08/12/2024	08/13/2024	-300	600
B	1200	08/12/2024	08/14/2024	50	1000
A	1000	08/12/2024	08/20/2024	0	600
C	1500	08/12/2024	08/20/2024	-500	1000

I want to add column "Result" for dynamically calculating the sum of "Value" and "Value 2" columns based on the "Date 2"(in ascending order) and corresponding "ID" columns. "Date 1" column will always be the same day (today) but it has duplicated IDs and Values. What I need is to calculate Result [1]= Value +Value 2 but then Rusult [2] = Result [1] + Value 2 [2].

Here is the screenshot of what the operations within the table should be like:

I have tried a few measures and tried looking up a similar question on Power BI forums but was not able to find anything like this.

Answer 1

Try this following as a Calculated Column:

Result = 
  var thisID = [ID]
  var thisDate2 = [Date 2]
  var totValue2 =
    CALCULATE(
      SUM([Value 2]),
      FILTER('YourTable', [ID] = thisID && [Date 2] <= thisDate2)
    )
  return [Value] + totValue2