Try to calculate PV in Dax - can any one help

[mohittimpus]

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. 

[iAm_ManCat · Accepted solution]

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

[v-shex-msft]

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

[AndrejZitnay]

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