cancel
Showing results for
Did you mean:
mohittimpus 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
iAm_ManCat 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: | |
AndrejZitnay 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

iAm_ManCat 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 Top Kudoed Authors
Users Online
Currently online: 308 members 3,018 guests
Recent signins:
• theresemurphy • gerald_bauer • zowie_thurlow • • MartijnNL • Cambridge1983 • • Alex_Key_SME • NastiaNiku • jdm-q • sanjanasharath9 • kamalwin • yasirsd • RonnyB 