Skip to main content
cancel
Showing results for 
Search instead 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

Reply
LooneyOMooney
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
v-jingzhang
Community Support
Community Support

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:

121801.jpg

 

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.

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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:

121801.jpg

 

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.

amitchandak
Super User
Super User

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

 

not sure it can help

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors