Payback calculation

OscarSuarez10 — Fri, 18 Oct 2019 16:01:39 GMT

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

Re: Payback calculation

AlB — Sat, 19 Oct 2019 09:33:57 GMT

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

topic Payback calculation in DAX Commands and Tips

Payback calculation

Re: Payback calculation