Calculate new column with aggregate amount

Question

I have a dataset like this:

Contract	KPI	Contract Extension	Date Start	Contract End Date	Amount Purchased	New ID Contract	New Amount Purchased
ID 1	A	No	01/01/2023	01/05/2023	1000	ID 1, ID 2, ID 3	1600
ID 1	B	No	01/01/2023	01/05/2023	1000	ID 1, ID 2, ID 3	1600
ID 2	A	ID 1	01/03/2023	01/05/2023	500	ID 1, ID 2, ID 3	1600
ID 2	B	ID 1	01/03/2023	01/05/2023	500	ID 1, ID 2, ID 3	1600
ID 3	A	ID 2	01/04/2023	01/05/2023	100	ID 1, ID 2, ID 3	1600
ID 4	A	NO	01/01/2023	01/07/2023	2000	ID 4, ID 5	2250
ID 5	A	ID 4	01/03/2023	01/07/2023	250	ID 4, ID 5	2250
ID 6	A	No	01/02/2023	01/08/2023	1200	ID 6	1200

For each Contract ID there is a total SMS purchased (Amount Purchased). Each contract can be extended by increasing the number of SMS purchased. My goal would be to calculate the "New ID Contract" and "New Amount Purchased" columns.

For example: contract ID 3 is the extension of contract ID 2, which in turn is the extension of id 1. So the aggregate total is 1600 for these three contracts (1000+500+100). When "Contract Extension" is populated with NO, it means that the contract in question is not an extension of another. When it is populated instead, the ID of the extended contract is indicated

So I should calculate the aggregate total for the shared contracts in the "New Amount Purchased" column and the shared contract IDs on "New ID Contract"

I wouldn't know how to set the code, so I have no attempt to show. I hope a solution is possible.

Thank you very much

Answer 1

We can utilize the "Parent and Child functions" in dax to help in this scenario.
Understanding functions for parent-child hierarchies in DAX.

Create the following Calculated Columns in order (4 in total).

// Need this one to replace "No" in [Contract Extension]
Parent Contract =
  IF([Contract Extension] = "No", BLANK(), [Contract Extension])


// Need this for the PATH
Contract Path =
  PATH( Contract[Contract], Contract[Parent Contract] )


New Amount Purchased = 
  var p1 = PATHITEM([Contract Path], 1)
  var tbl = SUMMARIZECOLUMNS('Contract'[Contract Path], 'Contract'[Amount Purchased])
  return SUMX(
    tbl,
    IF(PATHCONTAINS([Contract Path], p1), [Amount Purchased])
  )


New ID Contract = 
  var p1 = PATHITEM([Contract Path], 1)
  var tbl = SUMMARIZECOLUMNS('Contract'[Contract Path])
  var pMax = MAXX(
    tbl,
    IF(PATHCONTAINS([Contract Path], p1), [Contract Path])
  )
  return SUBSTITUTE(pMax, "|", ", ")