COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Changes in the July 2020 update to Power BI Desktop
Part two of a six-part series of blogs

This month's modest update allows you to use Excel financial functions in DAX, show a gradient legend and change your slicer header, among other minor changes.

  1. Changes in the July 2020 update to Power BI Desktop
  2. Using financial functions from Excel in Power BI (this blog)
  3. Gradient legends
  4. Customisable slicer headers
  5. Relative time filters
  6. Q + A updates

We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Posted by Andy Brown on 23 July 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using financial functions from Excel in Power BI

Excel has a host of functions for calculating interest rates, depreciation, pricing, yields and the value of investments.  Now so too does Power BI!

Example of DAX financial function

An example of a DAX financial function: calculating the monthly mortgage payment for a range of different borrowing scenarios.

Microsoft have imported "almost all" Excel financial functions into DAX, and kept the same argument signature for each:

The PMT function

The PMT function, as one example - the list of arguments is identical to those in the Excel equivalent.

Here are the functions you can use:

ACCRINT, ACCRINTM, AMORDEGRC, AMORLINC, COUPBAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD, CUMIPMT, CUMPRINC, DB, DDB, DISC, DOLLARDE, DOLLARFR, DURATION, EFFECT, FV, INTRATE, IPMT, ISPMT, MDURATION, NOMINAL, NPER, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PDURATION, PMT, PPMT, PRICE, PRICEDISC, PRICEMAT, PV, RATE, RECEIVED, RRI, SLN, SYD, TBILLEQ, TBILLPRICE, TBILLYIELD, VDB, YIELD, YIELDDISC, YIELDMAT.

You can also use XIRR and XNPV, but these were already included within the DAX range of functions.

This blog has 0 threads Add post