Payback calculation

[OscarSuarez10]

Hello i have to calculate the payback period per TEST and ID  using the following equation (usgin DAX):

PayBack = Last year with negative accumulated cash flow  +  (absolute value of the last negative accumulated cash flow / cash flow value in the following period)

The data is in the following table:

ID	TEST	YEAR	CASH FLOW	ACCUMULATED CASH FLOW
0	1	1	-5.446.900,00	-$ 44.963.300,00
0	1	2	48.374.800,00	$ 67.583.600,00
0	1	3	34.778.600,00	$ 143.859.400,00
0	1	4	24.837.100,00	$ 192.896.790,00
0	1	5	15.513.500,00	$ 217.445.668,00
0	1	6	7.799.090,00	$ 223.557.968,00
10	2	1	-5.418.360,00	-$ 44.933.160,00
10	2	2	48.228.600,00	$ 67.559.840,00
10	2	3	35.021.100,00	$ 144.020.240,00
10	2	4	24.933.700,00	$ 193.430.310,00
10	2	5	15.350.400,00	$ 217.839.289,00

Payback:  TEST 1 ID 0 = 1.93 years

                TEST 2 ID 10 = 1.93 years 

[AlB · Accepted solution]

Hi @OscarSuarez10 

Measure = 
VAR Year_ = CALCULATE(MAX(Table1[YEAR]);Table1[ACCUMULATED CASH FLOW] < 0)
VAR Numerator_ = CALCULATE(DISTINCT(Table1[ACCUMULATED CASH FLOW]);Table1[YEAR] = Year_) 
VAR YearNext_ = CALCULATE(MIN(Table1[YEAR]);Table1[YEAR] > Year_)
VAR Denominator_ = CALCULATE(DISTINCT(Table1[CASH FLOW]);Table1[YEAR] = YearNext_)
RETURN
Year_ + DIVIDE(Numerator_; Denominator_)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers