cancel
Showing results for
Did you mean:
Highlighted
Helper III

## 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
Highlighted
Super User III

## 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

Highlighted
Super User III

## 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

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors