cancel
Showing results for
Did you mean:
Highlighted
OscarSuarez10 Member

## Payback calculation

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

1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Payback calculation

```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  Super User

## Re: Payback calculation

```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 Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  Top Kudoed Authors
Users Online
Currently online: 282 members 2,814 guests
Recent signins:
• luwyli • Choctawgirl77 • MartyMcfly810 • SaddleGeek • MCKERY • nabarkve • krshaffer • tracyjouett • quang88 • maspbury • main_glego • samtim • primaveranz • CdeNysschen1 