DAX - XNPV (Help I have edited ad re-worded my original post to make it more understandable)

[cottrera]

Morning

I am stuggling with an XNPV result as it is not computing what I would expect.

I have a table with property reference, address, income, discount rate and period (Over 30 years) The example tabe below has a two discount rates that changes change over time it starts at 0.0371 and changes to 0.027

When I run the calcualtions in excel by using to the power of 

^

The result is different to XNPV in power bi.

Property Ref	Address	Sum ^ 😀 (Power of excel)	XNPV power Bi 😣
26	100 DAX STREET	145,781	210,600
18	50 CALCULATE ROAD	132,823	191,880

It looks like when I use XNPV its just multiplying the Income by the number of periods

for exmple Address 100 Dax Street has an income of 7020 x by 3o years = 210,600

which is the result XNPV is giving me.

Here is the function I am using

Income NPV =

XNPV('XNPV Community',

'XNPV Community'[Income],

'XNPV Community'[Period],

SELECTEDVALUE('XNPV Community'[Discount Rate]))

Property Ref	Address	Income	Period	Discount Rate
26	100 DAX STREET	7,020	01/01/2021	0.037
26	100 DAX STREET	7,020	01/01/2022	0.037
26	100 DAX STREET	7,020	01/01/2023	0.037
26	100 DAX STREET	7,020	01/01/2024	0.037
26	100 DAX STREET	7,020	01/01/2025	0.027
26	100 DAX STREET	7,020	01/01/2026	0.027
26	100 DAX STREET	7,020	01/01/2027	0.027
26	100 DAX STREET	7,020	01/01/2028	0.027
26	100 DAX STREET	7,020	01/01/2029	0.027
26	100 DAX STREET	7,020	01/01/2030	0.027
26	100 DAX STREET	7,020	01/01/2031	0.027
26	100 DAX STREET	7,020	01/01/2032	0.027
26	100 DAX STREET	7,020	01/01/2033	0.027
26	100 DAX STREET	7,020	01/01/2034	0.027
26	100 DAX STREET	7,020	01/01/2035	0.027
26	100 DAX STREET	7,020	01/01/2036	0.027
26	100 DAX STREET	7,020	01/01/2037	0.027
26	100 DAX STREET	7,020	01/01/2038	0.027
26	100 DAX STREET	7,020	01/01/2039	0.027
26	100 DAX STREET	7,020	01/01/2040	0.027
26	100 DAX STREET	7,020	01/01/2041	0.027
26	100 DAX STREET	7,020	01/01/2042	0.027
26	100 DAX STREET	7,020	01/01/2043	0.027
26	100 DAX STREET	7,020	01/01/2044	0.027
26	100 DAX STREET	7,020	01/01/2045	0.027
26	100 DAX STREET	7,020	01/01/2046	0.027
26	100 DAX STREET	7,020	01/01/2047	0.027
26	100 DAX STREET	7,020	01/01/2048	0.027
26	100 DAX STREET	7,020	01/01/2049	0.027
26	100 DAX STREET	7,020	01/01/2050	0.027
18	50 CALCULATE ROAD	6,396	01/01/2021	0.037
18	50 CALCULATE ROAD	6,396	01/01/2022	0.037
18	50 CALCULATE ROAD	6,396	01/01/2023	0.037
18	50 CALCULATE ROAD	6,396	01/01/2024	0.037
18	50 CALCULATE ROAD	6,396	01/01/2025	0.027
18	50 CALCULATE ROAD	6,396	01/01/2026	0.027
18	50 CALCULATE ROAD	6,396	01/01/2027	0.027
18	50 CALCULATE ROAD	6,396	01/01/2028	0.027
18	50 CALCULATE ROAD	6,396	01/01/2029	0.027
18	50 CALCULATE ROAD	6,396	01/01/2030	0.027
18	50 CALCULATE ROAD	6,396	01/01/2031	0.027
18	50 CALCULATE ROAD	6,396	01/01/2032	0.027
18	50 CALCULATE ROAD	6,396	01/01/2033	0.027
18	50 CALCULATE ROAD	6,396	01/01/2034	0.027
18	50 CALCULATE ROAD	6,396	01/01/2035	0.027
18	50 CALCULATE ROAD	6,396	01/01/2036	0.027
18	50 CALCULATE ROAD	6,396	01/01/2037	0.027
18	50 CALCULATE ROAD	6,396	01/01/2038	0.027
18	50 CALCULATE ROAD	6,396	01/01/2039	0.027
18	50 CALCULATE ROAD	6,396	01/01/2040	0.027
18	50 CALCULATE ROAD	6,396	01/01/2041	0.027
18	50 CALCULATE ROAD	6,396	01/01/2042	0.027
18	50 CALCULATE ROAD	6,396	01/01/2043	0.027
18	50 CALCULATE ROAD	6,396	01/01/2044	0.027
18	50 CALCULATE ROAD	6,396	01/01/2045	0.027
18	50 CALCULATE ROAD	6,396	01/01/2046	0.027
18	50 CALCULATE ROAD	6,396	01/01/2047	0.027
18	50 CALCULATE ROAD	6,396	01/01/2048	0.027
18	50 CALCULATE ROAD	6,396	01/01/2049	0.027
18	50 CALCULATE ROAD	6,396	01/01/2050	0.027

Thank you

RIchard

[v-luwang-msft]

Hi @cottrera ，

 (Year 1 - 5 discount rate = 0.0371,  Year 6 - 30 discount rate = 0.027)  But I see the data,when 2025 year ,the data is 0.027,could you pls share your excel.Remember to remove confidential data.

Best Regards

Lucien

[cottrera]

Hi Lucien

Im so sorry about the delay repsonding. i did not recieve notification.

This sound stupid but I cannot see where I can attach the excel file to this thread

can you please advise

Richard

[cottrera]

Sorry I forgot to say that I am using the following DAX function  

Income NPV =

XNPV('XNPV Community',

'XNPV Community'[Income],

'XNPV Community'[Period],

SELECTEDVALUE('XNPV Community'[Discount Rate]))