NPV with different discount rate and starting years for different products

[milkywaypowerbi]

Hi 

I currently have this bi file and I want to discount to achieve NPV. Link to pbix: https://drive.google.com/drive/folders/1Set9tyWE3x73MBN5evLJATXR274ibR_u?usp=sharing

The discount rate for Machine is 12% and 8% for Stationery. Machine discount rates start from 2019 while stationery starts from 2020.

For instance for machine I would like to discount the gross profit by 12% every year.

For 2019-> 1/(1+12%)^0* gross proft 2019

For 2020 -> 1/(1+12%)^1* gross profit 2020

For 2021 -> 1/(1+12%)^2 * gross profit 2021

For 2022 ->1/(1+12%)^3 * gross profit 2022

and so on.

For stationery, I would like to divide by a 8% rate instead of 12% in order to achieve the Net Present Value(NPV)

For 2020 -> 1/(1+8%)^0* gross proft 2020

For 2021-> 1/(1+8%)^1* gross profit 2021

For 2022 -> 1/(1+8%)^2 * gross profit 2022

For 2023 ->1/(1+8%)^3 * gross profit 2023

How can I achieve the NPV with different rates and years for each product Family? Thanks!

[v-yiruan-msft]

Hi @milkywaypowerbi ，

You can update the formula of your measure [NPV] as below, please find the details in the attachment.

NPV = 
VAR _selfamily =
    SELECTEDVALUE ( 'Dim Family'[Family] )
VAR _rate =
    SWITCH ( _selfamily, "Machine", 0.12, "Stationery", 0.08 )
VAR _selyear =
    SELECTEDVALUE ( 'Dim Year'[L1.year] )
VAR _diffyear =
    _selyear - SWITCH ( _selfamily, "Machine", 2019, "Stationery", 2020 )
RETURN
    IF (
        ISBLANK ( _diffyear )
            || _diffyear < 0,
        BLANK (),
        DIVIDE ( 1, POWER ( 1 + _rate, _diffyear ), 0 ) * 'Main Measures'[Revenue]
    )

Best Regards

[milkywaypowerbi]

Hello!

Thank you so much,

However, I would need a total as I would like to display NPV as a sum. So the total column should add up across all the years. How can I achieve that? And I think the NPV values are incorrect. Pls refer to the following calculations I have done on Excel. The NPV should have a smaller value than the gross profit since we are discounting.

Thank you once again!

[v-yiruan-msft · Accepted solution]

Hi @milkywaypowerbi ,

I update the formula of measure [NPV] (the part with red font is updated one) and create another new measure as below base on measure [NPV]. And put the new measure on the matrix to replace the original measure [NPV],now the matrix can display the same data with the one in your excel file... Please find the details in the attachment.

NPV =

VAR _selfamily =

SELECTEDVALUE ( 'Dim Family'[Family] )

VAR _rate =

SWITCH ( _selfamily, "Machine", 0.12, "Stationery", 0.08 )

VAR _selyear =

SELECTEDVALUE ( 'Dim Year'[L1.year] )

VAR _diffyear =

_selyear - SWITCH ( _selfamily, "Machine", 2019, "Stationery", 2020 )

RETURN

IF (

ISBLANK ( _diffyear )

|| _diffyear < 0,

BLANK (),

DIVIDE ( 1, POWER ( 1 + _rate, _diffyear ), 0 ) * 'Main Measures'[Profit]  // before used the measure Main Measures'[[Revenue]

)

Measure = SUMX(VALUES('Dim Year'[L1.year]),[NPV])

Best Regards

[milkywaypowerbi]

Hi,

Sorry I have another question. I am using Power Pivot on Excel to do the exact same thing using DAX formulas. I know that I have to swap the SELECTEDVALUES to IF(HASONEVALUE) since power pivot does not support selectedvalues but the formula no longer works on power pivot DAX.

[milkywaypowerbi]

Hi!

 Thank you so much!