cancel
Showing results for
Did you mean:
Regular Visitor

## Try to calculate PV in Dax - can any one help

This Formula for PV in Excel Sheet: =PV(0.15/12,48,-B4,0,0)

I want same thing need to calulcate in Dax power BI dekstop.

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Try to calculate PV in Dax - can any one help

Hello everyone,

I have resolved this at work only moments ago, so I thought I'd share as I couldn't find the answer on here!

So firstly we use the formula that MS provides for what PV is:

Then we derive what a value for PV would look like:

So if we create a new table with rate, nper, pmt and a known-good value to compare to, we can work out this formula (knowing that type =0 for payments at end of term and that fv=0 if we are not specifying a future value outright):

PVColumn = (

-

( [pmt]*( 1+ ( ([Rate]/12.0)*0) ) )

*

( ( (POWER( (1 + ([Rate]/12.0) ), [nper]) ) -1)

/

( [Rate]/12.0)

)

)

/

(POWER( (1 + ([Rate]/12.0) ), [nper]) )

I have stored and linked the .pbix Proof of Concept file for you to look at on my personal site,

Also worth noting that I used [Rate]/12.0 as we were calculating per month, and to work out yearly you could remove all of the /12.0

If anyone has any further questions or wants me to re-write it to include Type and FV, just let me know,

Cheers,

ManCat

3 REPLIES 3
Community Support Team

## Re: Try to calculate PV in Dax - can any one help

Hi @mohittimpus,

After I checked dax functions documents, I find out XNPV function to deal with cash flow. Maybe you can use it to instead excel pv function:

XNPV Function

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Member

## Re: Try to calculate PV in Dax - can any one help

Hello @v-shex-msft

but how? I have exactly same issue.

This is my example :

=-PV(C6/12,C4,C3)

C6 = 5%

C4=24

C3=300

result should be 6838.17

thanks.

Andrej

Frequent Visitor

## Re: Try to calculate PV in Dax - can any one help

Hello everyone,

I have resolved this at work only moments ago, so I thought I'd share as I couldn't find the answer on here!

So firstly we use the formula that MS provides for what PV is:

Then we derive what a value for PV would look like:

So if we create a new table with rate, nper, pmt and a known-good value to compare to, we can work out this formula (knowing that type =0 for payments at end of term and that fv=0 if we are not specifying a future value outright):

PVColumn = (

-

( [pmt]*( 1+ ( ([Rate]/12.0)*0) ) )

*

( ( (POWER( (1 + ([Rate]/12.0) ), [nper]) ) -1)

/

( [Rate]/12.0)

)

)

/

(POWER( (1 + ([Rate]/12.0) ), [nper]) )

I have stored and linked the .pbix Proof of Concept file for you to look at on my personal site,

Also worth noting that I used [Rate]/12.0 as we were calculating per month, and to work out yearly you could remove all of the /12.0

If anyone has any further questions or wants me to re-write it to include Type and FV, just let me know,

Cheers,

ManCat

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 308 members 3,018 guests
Recent signins: