XNPV through time

[cmotta]

Hi,

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

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.

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

Thanks!

[v-sihou-msft · Accepted solution]

@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. 

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,