Forum Discussion

OscarSuarez10's avatar
OscarSuarez10
Helper III
6 years ago
Solved

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 neg...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    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  Datanaut

AlB's avatar
AlB
Community Champion
6 years ago

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  Datanaut