How to caculate the net present value (npv) without a timeseries table

[LarsMartin]

Hi,

I am still relatively new to PowerBi DAX and would like to calculate the net present value for each row without an existing time series. This is what my data looks like.

Implementation measure	Sum of Investment	Sum of savings p.a.	period	rate	Net present value
21.6 kWp system (roof 1 + roof 2 & 32 kW water-water heat pump	42600	8150	15	15%
Air-water heat pump with 65°C flow temperature	34000	1797	20	10%
Air-water heat pump with temperature reductions to 45°C	44000	1496	15	10%
Air-water heat pump with temperature reductions to 55°C	44000	190	10	15%

The table must be in direct query mode.

I have some Implementation measures with an investment, savings per anno, rate and a period of time in years. The npv should be calculated for each row. The capital return for each year is equal to the savings p.a.. over the time in the column "period".

What I have tried so far:

1. Use the xnpv funktion in DAX. The funktion needs a table with the values, dates and a rate.
Within the definition of the calculated column, I created a table with a time series and the value of the savings. This worked on the DAX.do website, but it seems to work only for measures and not for calculated columns.

(Example for the first row of my table):

VAR rate= 0.15
TABLE Zeitreihe = ADDCOLUMNS  (
            GENERATESERIES (
                2021,
                2035,
                1
            ), "year" , DATE([Value],01,01) ,
                "_value" , 8150)
        )
VAR NBW = -42600 + XNPV (Zeitreihe,[_value],[year],rate)

RETURN
{    
    NBW
}

2. Tried to to do a DAX for the calculated column with some kind of a loop and calculate the npv with the formula

npv= Investment + Sum over period (value/(1+rate)^time).

But I do not know how to loop over the period.

Best regards 

Lars

[bhanu_gautam]

@LarsMartin , Try using

Net Present Value =
VAR Investment = [Sum of Investment]
VAR Savings = [Sum of savings p.a.]
VAR Rate = [rate]
VAR Period = [period]
VAR DiscountedSavings =
SUMX(
GENERATESERIES(1, Period, 1),
Savings / POWER(1 + Rate, [Value])
)
RETURN
-Investment + DiscountedSavings

[LarsMartin]

Hi,

thamnks for the fast reply, but I get the error: "Function 'SUMX' is not allowed as part of calculated column DAX expressions on DirectQuery models."

As stated above, I have to do it in DirectQueryMode.

Best regards

Lars

[johnt75]

I think you can create a calculated column like

Net present Value Column Table =
VAR rate = 'Table'[Rate]
VAR StartYear =
    YEAR ( 'Table'[Investment date] )
VAR EndYear = StartYear + 'Table'[Period]
VAR Zeitreihe =
    ADDCOLUMNS (
        GENERATESERIES ( StartYear, EndYear, 1 ),
        "year", DATE ( [Value], 01, 01 ),
        "_value", 'Table'[Savings p.a.]
    )
VAR NBW =
    ( -1 * 'Table'[Investment Amount] )
        + XNPV ( Zeitreihe, [_value], [year], rate )
RETURN
    { NBW }