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

[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 }

[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

[v-menakakota]

Hi @LarsMartin ,
Thanks for reaching out to the Microsoft fabric community forum. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). 

Do not include sensitive information. Do not include anything that is unrelated to the issue or question. 

Please show the expected outcome based on the sample data you provided. 

Best Regards, 
Community Support Team 

[LarsMartin]

Hi @v-menakakota ,

thanks for your reply. What aditional information do you need?

In my first post I added a table (not a picture) with all the information needed to calculate the npv. I also added the DAX expresion as code.

So I honestly do not know what else to provide.

Best regards

Lars

[v-menakakota]

Hi @LarsMartin ,

Can you please try this measure.

PV =

VAR Inv = SELECTEDVALUE( Test[Sum of Investment] )

VAR Sav = SELECTEDVALUE( Test[Sum of savings p.a.] )

VAR RateRaw = SELECTEDVALUE( Test[rate] )  

VAR Rate = DIVIDE( RateRaw, 100, 0 )          

VAR Period = SELECTEDVALUE(Test[period ], 0 )

RETURN

IF(

    OR(

        ISBLANK(Inv),

        OR( ISBLANK(Sav), Period <= 0 )

    ),

    BLANK(),

    -Inv +

    SUMX(

        GENERATESERIES(1, Period, 1),

        Sav / POWER( 1 + Rate, [Value] )

    )

)

Best Regards, 
Community Support Team 

[v-menakakota]

Hi @LarsMartin ,

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

If I misunderstand your needs or you still have problems on it, please feel free to let us know.   

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @LarsMartin ,

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

Best Regards, 
Community Support Team

[LarsMartin]

Hi, 

and thanks for the reply. I was on vaccation last week, so I will try it thius week.

Thanks.

[v-menakakota]

Hi @LarsMartin ,

Thank you for the update.