cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Present value of future cashflows

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] )
)

1 ACCEPTED SOLUTION
Community Support

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.

2 REPLIES 2
Community Support

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.

Super User

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

not sure it can help

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

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

Fabric Community Update - June 2024

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors