Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cmotta
Regular Visitor

XNPV through time

Hi,

 

I have the following cash flow, and I need the npv value of it through time.

3.PNG

 

I have tried using xnpv, but either I get the npv of just the period or I get the npv of the whole thing. What I need is the npv of future cashflows. The closest I have gotten is using 

 

CALCULATE(
XNPV(cf,(CF[Forecast])*-1,CF[Date],.15)
,filter(ALL(CF),
CF[Date]>=max(CF[Date]))) 

 

However, that has two main problems. One is that at the end it just goes back to the npv of the whole thing instead of 0 or ending, and second that it is just seems to be calculating at the points where there is data, and not every month. 

The black line is that it should look like (I did it in excel and imported it), and the other line is the formula I pasted.

 

 

 

2.PNG

 

So the question is: How can I calculate XNPV of all and only the future months?

 

Thanks!

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@cmotta

 

1. One is that at the end it just goes back to the npv of the whole thing instead of 0 or ending

 

I haven't reproduced your issue. It just typed 6 rows as your sample. The measure goes to the end properly in line chart. 

 

44.PNG

 

2. it is just seems to be calculating at the points where there is data, and not every month. 

 

This issue should be solved by add a full calendar table and build relationship to your CF table on Date column.

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@cmotta

 

1. One is that at the end it just goes back to the npv of the whole thing instead of 0 or ending

 

I haven't reproduced your issue. It just typed 6 rows as your sample. The measure goes to the end properly in line chart. 

 

44.PNG

 

2. it is just seems to be calculating at the points where there is data, and not every month. 

 

This issue should be solved by add a full calendar table and build relationship to your CF table on Date column.

 

Regards,

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.