Present value of future cashflows

[LooneyOMooney]

I am trying to solve a present value problem using DAX. 

I have a series of future cashflows and the dates of these cashflows in two columns in a table.  I would like to know, at a defined discount rate, what the present value values of one year's worth of cashflows is ten years' time. 

For example, I am only interested in the cashlows between 1st Jan 2030 (start date) and 31st Jan 2030 (end date) and I wish to discount these cashflows at 10% per year.  For ease of example, let's say there is a single US$1m cash inflow on 31st Jan 2030.  At a discount rate of 10% per annum the present value of that million dollars in today's money is US$494,215.  The discount rate is provided by a slicer (as I need to retain the ability to change the rate).  So far I have the following DAX syntax below in a measure but it provides me with an incorrect result - essentially because an accurate XNPV cashflow function requires an initial cash outflow (i.e. having a cashflow value of zero today as well as the US$1m inflow in ten years' time would result in the correct number).

If anyone has experince of PV calculations in DAX or how to resolve this issue I'd appreciate some pointers.  The PV function doesn't appear to be available.

Measure =
CALCULATE(XNPV(MyTable,
MyTable[Cashflow],
MyTable[Date],
DiscountRate[discount rate]),
FILTER(MyTable,
MyTable[Description] IN {"Some Value"}
&& MyTable[Date] >= [start date]
&& MyTable[Date] <= [end date] )
)

[v-jingzhang · Accepted solution]

Hi @LooneyOMooney 

Please try the following measures. In this way, an initial cash flow value is not required. Download PBIX from here.

Denominator = 
POWER (
    1 + SELECTEDVALUE ( DiscountRate[DiscountRate], 0.1 ),
    DIVIDE ( SELECTEDVALUE ( MyTable[Date] ) - TODAY (), 365 )
)

PV_single = DIVIDE ( SELECTEDVALUE ( MyTable[Cashflow] ), [Denominator] )

PV_total = 
VAR start_date = DATE ( 2030, 1, 1 )
VAR end_date = DATE ( 2030, 1, 31 )
VAR filter_table =
    FILTER (
        ALL ( MyTable ),
        MyTable[Description]
            IN { "Some Value" }
            && MyTable[Date] >= start_date
            && MyTable[Date] <= end_date
    )
RETURN
    SUMX ( filter_table, [PV_single] )

Result:

Kindly let me know if this helps.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[MFA]

Can't download the file anymore 

[amitchandak]

@LooneyOMooney , I have used it my video here- https://www.youtube.com/watch?v=9VYqbj2h4zE

not sure it can help