{"id":88307,"date":"2020-09-19T19:56:33","date_gmt":"2020-09-19T19:56:33","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=88307"},"modified":"2024-09-03T20:15:21","modified_gmt":"2024-09-03T20:15:21","slug":"introduction-to-dax-financial-functions-part-1","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/introduction-to-dax-financial-functions-part-1\/","title":{"rendered":"DAX financial functions: Loan calculations"},"content":{"rendered":"<p><strong>The series so far:<\/strong><\/p>\n<ol>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/introduction-to-dax-financial-functions-part-1\/\">DAX financial functions: Loan calculations<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/dax-financial-functions-depreciation-calculations\/\">DAX financial functions: Depreciation calculations<\/a><\/li>\n<\/ol>\n\n<p>In this article, I will introduce the <strong>DAX financial functions<\/strong>. These 50-plus functions debuted around the time of the July 2020 release of Power BI Desktop. DAX financial functions are largely derived from those found in Excel, and so will seem familiar to many of you. A large number of this sizable number of functions are most useful in relatively specialized scenarios. I\u2019ll be focusing, within this multi-part series, upon the more popular functions \u2013 popular because they concentrate upon lending, borrowing, and monitoring money, the lifeblood of virtually all business ventures.<\/p>\n<p>Say you have a client who has contacted you to ask for an introduction to the new functions, preferring that you deliver quick overviews for the most popular, based upon the widespread use of their Excel counterparts. Because you agree that a \u201clunch-and-learn\u201d format will be most accessible to the handful of Power BI authors in accounting and finance, you decide to parcel your introduction over short sessions, each of which will group a few related functions together, with practice examples based upon a small\u00a0\u00a0<a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/ST_P01_DAXFinFunc.pbix\">dataset<\/a>.<\/p>\n<p>I\u2019ll kick off this introduction\u00a0with a small subset of the DAX financial functions that are used regularly within a popular area of analysis and reporting: the performance of calculations with loans. To this end, this article focuses upon the following DAX financial functions:<\/p>\n<ul>\n<li>PMT()<\/li>\n<li>RATE()<\/li>\n<li>NPER()<\/li>\n<li>PV()<\/li>\n<li>FV()<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"825\" height=\"212\" class=\"wp-image-88308\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-109.png\" \/><\/p>\n<p><strong>Illustration 1: The Focus in this Article is a Handful of Loan-Related Financial Functions<\/strong><\/p>\n<p>As a part of this introduction, you\u2019ll have an opportunity to examine how each function can be employed to support business requirements of the sort that your hypothetical colleagues encounter routinely, and, for the most part, accomplish with Microsoft Excel, in meeting regular business requirements. You\u2019ll learn the purpose of each function, and then undertake a practice example with each that demonstrates how it interacts with a small loan data set, via a calculation that you construct. Moreover, you will:<\/p>\n<ul>\n<li>Examine the syntax involved in exploiting the function.<\/li>\n<li>Undertake an illustrative example of the use of the function in a practice exercise.<\/li>\n<li>Review a brief discussion of the results you obtain in the steps of the practice example.<\/li>\n<\/ul>\n<h2>Preparation for the Practice Exercises in this Level<\/h2>\n<p>Assuming that you have installed <strong>Power BI Desktop<\/strong> (the illustrations in this article reflect the August 2020 release), you are ready to download and open the sample <strong>Power BI Desktop<\/strong> file. You will use the file for hands-on practice with the concepts introduced in the sections that follow.<\/p>\n<p>NOTE: The latest version of Power BI is available for free download at <a href=\"http:\/\/www.powerbi.com\">www.powerbi.com<\/a>.<\/p>\n<p>Download and Open the Sample Power BI File (.pbix) for Use in this Article<\/p>\n<p>The small<a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/ST_P01_DAXFinFunc.pbix\"> <strong>sample Power BI file<\/strong><\/a> you\u2019ll be using contains enough imported data to support practice exercises for the functions covered in this article. You\u2019ll add the calculations and visualizations upon which the article focuses as you go. Using the sample dataset provided will ensure that the results you obtain in following the detailed steps of the exercises agree to the results obtained (and depicted) as you progress through the individual sections.<\/p>\n<p>Once the sample <strong>.pbix<\/strong> file is downloaded, take the following steps to open it in <strong>Power BI Desktop<\/strong>.<\/p>\n<ol>\n<li>Open <strong>Power BI Desktop<\/strong>.<\/li>\n<li><strong>Select File &#8211; Open other reports <\/strong>from the splash dialog that appears upon entry, as shown.<\/li>\n<\/ol>\n<p><strong><img loading=\"lazy\" decoding=\"async\" width=\"480\" height=\"254\" class=\"wp-image-88309\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-110.png\" \/><\/strong><\/p>\n<p><strong>Illustration 2: Select Open Other Reports on the Splash Dialog that Appears<\/strong><\/p>\n<ol start=\"3\">\n<li>Navigate to the downloaded .pbix file.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"445\" height=\"300\" class=\"wp-image-88310\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-111.png\" \/><\/p>\n<p><strong>Illustration 3: Select the Downloaded File and Open \u2026<\/strong><\/p>\n<ol start=\"4\">\n<li>Click <strong>Open<\/strong>.<\/li>\n<\/ol>\n<p>The .pbix file opens, and you arrive within the <strong>Report<\/strong> view, which consists of a single tab containing a blank canvas. As many of you are aware, you can tell you are in the <strong>Report <\/strong>view because the current view (of the three views available in the upper left corner, <strong>Report<\/strong>, <strong>Data<\/strong>, and <strong>Model<\/strong>) is indicated by the yellow bar to the left of the icon.<\/p>\n<ol start=\"5\">\n<li>Click the <strong>Data<\/strong> view icon along the left of <strong>Power BI Desktop<\/strong>, as desired, to become familiar with the basic sample model.<\/li>\n<\/ol>\n<p>At this point, you\u2019ll construct a table visualization to contain loan details to which you will need to be able to easily refer to create calculations using DAX financial functions in the practice examples. This table will also serve as a means of verifying your results for each of the calculations you create.<\/p>\n<p>Construct a Table Visualization to Contain Loan Details for Reference and as an \u201cAnswer Key\u201d<\/p>\n<ol>\n<li>In the sample Power BI model, make sure to be in the<strong> Report view<\/strong>.<\/li>\n<li>Click the cursor in the upper half the blank canvas.<\/li>\n<li>Click the<strong> Table<\/strong> icon in the collection atop the <strong>Visualizations<\/strong> tab, to create a blank table on the canvas.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"900\" height=\"339\" class=\"wp-image-88311\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-112.png\" \/><\/p>\n<p><strong>Illustration 4: Create a New Table Visualization on the Canvas<\/strong><\/p>\n<ol start=\"4\">\n<li>Ensuring that the above table visualization is selected, add the following fields (from the <strong>Loans<\/strong> table in the <strong>Fields<\/strong> pane) to the <strong>Values<\/strong> section of the <strong>Fields<\/strong> tab of the <strong>Visualizations<\/strong> pane:\n<ul>\n<li>LoanID<\/li>\n<li>LoanName<\/li>\n<li>Life (Yrs)<\/li>\n<li>Pd<\/li>\n<li>Rate % &#8211; Annual<\/li>\n<li>Pmt<\/li>\n<li>Rate<\/li>\n<li>Nper<\/li>\n<li>Pv<\/li>\n<li>Type<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p>The fields appear in the table as depicted.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"467\" class=\"wp-image-88312\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-113.png\" \/><\/p>\n<p><strong>Illustration 5: Additions in the Values Section of the Fields Tab<\/strong><\/p>\n<p>Finally, it\u2019s a good idea to label the table you\u2019re are creating \u2013 as I\u2019ve said throughout my <a href=\"https:\/\/www.sqlservercentral.com\/stairways\/stairway-to-dax-and-power-bi\">Stairway to DAX and Power BI<\/a> series and elsewhere. This is a minor point, but, as multiple visualizations tend to accumulate within a development environment, it is often helpful to make them easily distinguishable via descriptive, working titles. It\u2019s also a great way to identify the at-a-glance verification mechanism for other internal team members to use, say, in granting approval to promote a model and its contents to production from development.<\/p>\n<ol start=\"5\">\n<li>With the new table selected, once again, click the <strong>Format<\/strong> (paint roller) tab, underneath the visualizations collection atop the <strong>Visualizations<\/strong> pane.<\/li>\n<li>Scroll down to the <strong>Title<\/strong> section of the <strong>Format<\/strong> settings.<\/li>\n<li>Click the <strong>Title<\/strong> slider to<strong> On<\/strong>.<\/li>\n<li>Expand the <strong>Title<\/strong> section by clicking the carat to the left of the <strong>Title <\/strong>label.<\/li>\n<li>Type <strong>Loan Details<\/strong> in the <strong>Title <\/strong>text box.<\/li>\n<li>Set formatting as desired (you can see what I used in the illustration below).<\/li>\n<\/ol>\n<p>The settings for the <strong>Title<\/strong> section of the <strong>Format<\/strong> tab appear, alongside the new table, somewhat as depicted.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"635\" class=\"wp-image-88313\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-114.png\" \/><\/p>\n<p><strong>Illustration 6: Title Settings for the New Table<\/strong><\/p>\n<p>Finally, you can wrap up preparation for the practice exercises by constructing a table similar to the one above, to contain a calculation each for the DAX financial functions introduced in this article.<\/p>\n<p>Construct a Second Table Visualization to Contain Calculations You Create within the Practice Example<\/p>\n<p>You can use a quick \u201ccopy and customize\u201d action to create a table to house your practice examples.<\/p>\n<ol>\n<li>Click the table you created above on the canvas to select it.<\/li>\n<li>Select <strong>CTRL+C<\/strong> (Copy) to copy the existing table visualization.<\/li>\n<li>Click outside the table and onto the blank canvas.<\/li>\n<li>Select <strong>CTRL + V<\/strong> (Paste) on the keyboard to create an identical copy of the table you just created.<\/li>\n<li>Move the newly created copy above the original on the canvas to create working space, approximately as shown.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"825\" height=\"486\" class=\"wp-image-88314\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-115.png\" \/><\/p>\n<p><strong>Illustration 7: Copied Table above the Original \u2026<\/strong><\/p>\n<ol start=\"6\">\n<li>Ensuring that the new clone table (now above the original) remains selected on the canvas, deselect the following fields from the <strong>Values <\/strong>section of the <strong>Fields<\/strong> tab on the <strong>Visualizations<\/strong> tab to remove them from the new table visualization.\n<ul>\n<li>Pmt<\/li>\n<li>Rate<\/li>\n<li>Nper<\/li>\n<li>Pv<\/li>\n<li>Type<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<ol start=\"7\">\n<li>With the new table still selected, click the <strong>Format<\/strong> tab, once again, underneath the visualizations collection atop the <strong>Visualizations<\/strong> pane.<\/li>\n<li>Scroll down to the <strong>Title<\/strong> section of the <strong>Format<\/strong> settings.<\/li>\n<li>Expand the <strong>Title<\/strong> section by clicking the carat to the left of <strong>Title<\/strong> label.<\/li>\n<li>Change the <strong>Title<\/strong> from <strong>Loan Details<\/strong> to <strong>DAX Finance Calculations (&#8220;c_&#8221;)<\/strong>.<\/li>\n<li>Leave formatting as is.<\/li>\n<\/ol>\n<p>The clone table now appears, above the original table, as depicted.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"937\" height=\"377\" class=\"wp-image-88315\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-116.png\" \/><\/p>\n<p><strong>Illustration 8: The Clone becomes the Calculations Table \u2026<\/strong><\/p>\n<p>You now have a destination container for the calculations you will create with each DAX financial function introduced in the sections below.<\/p>\n<h2>Shared Parameters for DAX Financial Functions in Part 1<\/h2>\n<p>The financial functions introduced in this article are related. As you are about to see (and as many of you already know), each function is an argument (parameter) within the others. The information supplied within the <strong>Loan Details<\/strong> table above reflects the parameters for each loan in the group contained within your Power BI source table.<\/p>\n<p>Any one of these values can be computed via a DAX financial function using the other parameters. To gain an introduction to the operation of each function efficiently, you will work through a separate practice exercise for each function, where you can get hands-on exposure to the associated function as if it were missing from the data source, using the remaining parameters. As an example, you\u2019ll employ the <code>NPER()<\/code> function by treating the <code>Nper<\/code> value in the table as missing, and calculating it with the remaining values, within the individual <code>NPER()<\/code> introduction and practice section.<\/p>\n<p>For this reason, it makes sense to place an explanation of the shared arguments \/ parameters here, so as not to repeat them in the <strong>Syntax<\/strong> section of each function. Should you need to refresh your understanding of the meaning of any given argument, you need only refer to the below.<\/p>\n<p>The shared arguments \/ parameters are:<\/p>\n<ul>\n<li><strong>Rate<\/strong> \u2013 The interest rate per period.<\/li>\n<\/ul>\n<p>Example: Say an individual takes out a 30-year mortgage loan for a primary residence at a 3.5 percent annual interest rate. Assuming regular monthly payments, the interest rate is .035 \/ 12, or .002917 per month. .002917 would be added into the formula as the <strong>Rate<\/strong>.<\/p>\n<ul>\n<li><strong>Pmt<\/strong> \u2013 The payment made each period of, and which typically stays the same over, the life<\/li>\n<\/ul>\n<p>of the annuity. This payment usually includes principal and interest but no other fees or taxes.<\/p>\n<ul>\n<li><strong>Nper<\/strong> \u2013 The total <em>number<\/em> of payment periods in an annuity. (<em>Error occurs if less than 1.<\/em>)<\/li>\n<\/ul>\n<p>Example: In the case of an individual taking out a 30-year mortgage loan with monthly payments, the number of payment periods would be 360 (30 years times 12 months). 360 would be added into the formula as <strong>Nper<\/strong>.<\/p>\n<ul>\n<li><strong>Pv<\/strong> \u2013 The present value, or the total amount that a series of future payments is worth in<\/li>\n<\/ul>\n<p>present time. Present value is often called \u201cprincipal.\u201d<\/p>\n<p>Note: I get into present value in more depth in the discussion of the DAX <strong>PV()<\/strong> function below.<\/p>\n<ul>\n<li><strong>Fv<\/strong> \u2013 (Optional) The future value, or cash balance one expects to attain after the last<\/li>\n<\/ul>\n<p>payment is made (<strong>Fv<\/strong> is assumed to be blank, if omitted).<\/p>\n<p>Example: Say an individual determines to save $ 20,000 for a project she expects to begin in 7 years. $ 20,000 represents the future value. She could estimate a conservative range for the interest rate available, and, based upon that, determine how much to save each month.<\/p>\n<ul>\n<li><strong>Type<\/strong> \u2013 (Optional) The number 0 or 1. <strong>Type<\/strong> indicates <em>when payments are due<\/em>.<\/li>\n<\/ul>\n<p>Accepted values:<\/p>\n<ul>\n<li>0 (or blank) Payments are due at the end of the period\n<ul>\n<li style=\"list-style-type: none;\">\u00a0<\/li>\n<\/ul>\n<\/li>\n<li>1 Payments are due at the beginning of the period<\/li>\n<\/ul>\n<h2>DAX Financial Function: PMT()<\/h2>\n<p>According to the <em>Data<\/em> <em>Analysis Expressions (DAX) Reference<\/em>, the <code>PMT()<\/code> function \u201ccalculates the payment for a loan based on constant payments and a constant interest rate.\u201d <code>PMT()<\/code> is a highly popular function in Excel where finance professionals use it heavily in real estate and other lending modeling. Its underlying formula is the logic commonly found within loan payment calculators. <code>PMT()<\/code> calculates the payment made per period, for example, the monthly payment for a loan, or the monthly payment to an account set up to accumulate a savings goal.<\/p>\n<p>The basic idea is that, supplied with an interest rate, a number of periods (months in the examples you encounter here) and the total (or \u201cpresent,\u201d in the case of a loan) value, you have what you need to calculate the amount of the individual payments for a typical loan. (And yes, the financial environment is full of many variations, minor to exotic, which are beyond the scope of this introductory article.)<\/p>\n<h3>Syntax<\/h3>\n<p>Syntactically, the parameters you provide are specified within the parentheses to the right of <code>PMT<\/code> as shown:<\/p>\n<pre class=\"lang:tsql decode:true \">PMT(&lt;rate&gt;, &lt;nper&gt;, &lt;pv&gt;[, &lt;fv&gt;[, &lt;type&gt;]])<\/pre>\n<p>The arguments are explained in the section named <strong>Shared Parameters for DAX Financial Functions in Part 1<\/strong> above.<\/p>\n<h3>Return Value and Further Remarks<\/h3>\n<p><code>PMT()<\/code> returns a value that includes principle and interest only. Somewhat obviously, fees, taxes, insurance, reserve payments, and other costs associated with loans are computed and added outside this calculation.<\/p>\n<p>Keep in mind that the units for <strong>Rate<\/strong> and <strong>Nper <\/strong>must be consistent.<\/p>\n<p>Example: An individual makes <em>annual <\/em>payments on a 10-year loan, the annual interest rate for which is 6 percent. The <strong>Rate<\/strong>, of course, is .06, and the <strong>Nper<\/strong> is 10. If monthly payments are made for the same loan, <strong>Rate<\/strong> should be .06 \/ 12 (.005), and <strong>Nper<\/strong> is 10 x 12 (120).<\/p>\n<p>You\u2019ll get some hands-on practice with\u00a0<code>PMT()<\/code>\u00a0in Power BI in the next section.<\/p>\n<h3>Practice<\/h3>\n<p>The operation of <code>PMT()<\/code> will become clear using the data contained in the Power BI model you have downloaded and prepared above. You\u2019ll begin with the dataset that appears in the model, and create a calculation that employs <code>PMT()<\/code>, whose parameters are selected out of the loans data in the table provided. Along with the other functions you examine in this article, the \u201canswer\u201d to be expected via the calculation you create will already exist in static form in the table within the model. It will also appear within the <strong>Loan Details<\/strong> table visualization that you created in the preparation steps. <em>The functions within this article are largely dependent upon each other<\/em>, as you will see \u2013 and this is a great way to \u201cview all the cards simultaneously,\u201d while observing how each function works in DAX.<\/p>\n<p>Employ the DAX <strong>PMT()<\/strong> Function to Generate Basic Monthly Loan Payments<\/p>\n<p>You can take the following steps to create a calculation to return payments for various loans in the sample dataset.<\/p>\n<ol>\n<li>From the <strong>Report<\/strong> view, right click the <strong>Loans<\/strong> table in the <strong>Fields <\/strong>pane of the model.<\/li>\n<li>Select <strong>New column<\/strong> from the context menu that appears.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"764\" class=\"wp-image-88316\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-117.png\" \/><\/p>\n<p><strong>Illustration 9: Creating a New Calculation \u2026<\/strong><\/p>\n<ol start=\"3\">\n<li>Type, or cut and paste, the following into the <strong>Formula <\/strong>bar:<\/li>\n<\/ol>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">c_PMT =\r\nPMT(\r\n   Loans[Rate],\r\n      Loans[Nper],\r\n         Loans[Pv],\r\n            Loans[Fv],\r\n         Loans[Type]\r\n    )<\/pre>\n<p>The calculation appears as shown in the <strong>Formula<\/strong> bar:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"525\" height=\"354\" class=\"wp-image-88317\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-118.png\" \/><\/p>\n<p><strong>Illustration 10: Calculation Containing the PMT() Function \u2026<\/strong><\/p>\n<ol start=\"4\">\n<li>Click the checkmark to the left of the <strong>Formula <\/strong>bar to check and commit the calculation, and to create the new calculated column.<\/li>\n<\/ol>\n<p>The calculation <strong>c_PMT<\/strong> appears in the <strong>Loans<\/strong> table in the <strong>Fields<\/strong> pane.<\/p>\n<p>NOTE: You will be naming the calculations you create in this article with a <strong>c_<\/strong> prefix, perhaps somewhat obviously to leave their names very close to that of the DAX financial function they employ, while making them easily identifiable.<\/p>\n<ol start=\"5\">\n<li>With the new calculated column <strong>c_PMT <\/strong>selected in the <strong>Fields<\/strong> pane, make the following <strong>Format<\/strong> settings underneath the main menu:\n<ul>\n<li>Currency ($)<\/li>\n<li>2 decimal places<\/li>\n<li>Don\u2019t summarize<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"576\" height=\"78\" class=\"wp-image-88318\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-119.png\" \/><\/p>\n<p><strong>Illustration 11: Calculated Column Format Settings<\/strong><\/p>\n<ol start=\"6\">\n<li>Select the <strong>DAX Finance Calculations (&#8220;c_&#8221;)<\/strong> table visualization, once again, and add the new <strong>c_PMT <\/strong>calculation to the <strong>Values<\/strong> section of the <strong>Fields<\/strong> tab for the table, underneath the existing <strong>Rate % &#8211; Annual <\/strong>calculation.<\/li>\n<\/ol>\n<p>The <strong>Pmt <\/strong>value within the <strong>Loan Details<\/strong> table visualization serves as a means of checking the output accuracy of the new calculation, as shown in the current results.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"445\" class=\"wp-image-88319\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-120.png\" \/><\/p>\n<p><strong>Illustration 12: Verifying Accuracy of Value Returned via the New Calculation<\/strong><\/p>\n<p>If you\u2019ve obtained similar results to the above, you can conclude that you\u2019ve successfully assembled a calculation to demonstrate the operation of the <strong>DAX <\/strong><code>PMT()<\/code> financial function.<\/p>\n<p>In the next section, you\u2019ll be introduced to the DAX <code>RATE()<\/code> function.<\/p>\n<h2>DAX Financial Function: RATE()<\/h2>\n<p>According to the <em>Data<\/em> <em>Analysis Expressions (DAX) Reference<\/em>, the DAX <code>RATE()<\/code> financial function \u201creturns the interest rate per period of an annuity.\u201d <code>RATE()<\/code> can be used to calculate interest rates in scenarios ranging from determining the average annual rate of return one might earn from a bond purchased, to determining the percentage of amortization or depreciation per period, to calculating a given security\u2019s yield to maturity \u2013 and many other settings in between. Two of the more common uses to which <code>RATE()<\/code> is put might involve determining the interest rate required by a loan of a given amount, or to reach a targeted amount on an investment, over a specific period of time.<\/p>\n<p>You\u2019ll take a look at how <code>RATE()<\/code> works with the loan data you used as a source in the <code>PMT()<\/code> section earlier. You\u2019ll take up your practice with <code>RATE()<\/code> as if the rate itself is the only unknown variable, and use the <code>RATE()<\/code> function to calculate the periodic interest rate to pay off a stated present value, with an associated stated periodic payment for the loan, and with a stated total number of payments.<\/p>\n<h3>Syntax<\/h3>\n<p>Syntactically, the parameters you provide are specified within the parentheses to the right of <code>RATE<\/code> as shown:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">RATE(&lt;nper&gt;, &lt;pmt&gt;, &lt;pv&gt;[, &lt;fv&gt;[, &lt;type&gt;[, &lt;guess&gt;]]])<\/pre>\n<p>The arguments are explained in the section named <strong>Shared Parameters for DAX Financial Functions in Part 1<\/strong> above, with the exception of the <strong>Guess <\/strong>parameter, which only occurs in this function.<\/p>\n<ul>\n<li><strong>Guess<\/strong> \u2013 (Optional) A guess as to what the rate will be.\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>If omitted \/ blank, <strong>Guess<\/strong> is assumed to be 10%.<\/li>\n<li>If <strong>Rate<\/strong> does not converge, attempt different values for <strong>Guess<\/strong>. Per the documentation, \u201c<strong>Rate<\/strong> usually converges if <strong>Guess<\/strong> is between 0 and 1.\u201d A practical approach might be to simply place <strong>Rate<\/strong> \/<strong> Nper<\/strong> from the loan \/ other description into the <strong>Guess<\/strong> (leaving it blank has resulted in errors, in my experience, although the documentation states that Guess is \u201coptional.\u201d)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3>Return Value and Further Remarks<\/h3>\n<p><code>RATE()<\/code> returns the interest rate <em>per period,<\/em> a value that includes principle and interest only.<\/p>\n<p>Keep in mind that the units for specifying <strong>Guess <\/strong>and <strong>Nper<\/strong> must be consistent.<\/p>\n<p><strong>Rate<\/strong> and <strong>Nper <\/strong>must be consistent.<\/p>\n<p>Example: An individual makes <em>monthly <\/em>payments on a 20-year loan, the annual interest rate for which is 6 percent. In this case, you would supply a <strong>Guess<\/strong> of .06\/12, and 20*12 (or 240) for <strong>Nper<\/strong>.<\/p>\n<p>Errors can be returned if the<strong> Rate<\/strong>, after 20 iterations, does not converge to within 0.0000001, or if a value of zero or less is supplied for <strong>Nper<\/strong> (unsurprisingly).<\/p>\n<p>It\u2019s time to get some hands-on exposure to <code>RATE()<\/code> in Power BI.<\/p>\n<h3>Practice<\/h3>\n<p>You will return to the Power BI model you\u2019ve have prepared, and create a calculation in the <strong>Loans<\/strong> table that uses the <code>RATE()<\/code> function. The parameters, once again, for the function under examination, will be sourced from the <strong>Loans<\/strong> table within the model.<\/p>\n<p>Recall that the answer to be expected via the calculation you create already exists in static form in the table within the model. The <strong>Loan Details<\/strong> table visualization that you created in the preparation steps contains this and other parameter values for easy visual validation.<\/p>\n<p>Employ the DAX <strong>RATE()<\/strong> Function to Return the Monthly Interest Rates for a Group of Loans<\/p>\n<p>You\u2019ll create a calculation to return the respective periodic (monthly) interest rates for the loans in the sample dataset.<\/p>\n<ol>\n<li>From the <strong>Report<\/strong> view, right-click the <strong>Loans<\/strong> table in the <strong>Fields <\/strong>pane of the model, once again.<\/li>\n<li>Select <strong>New column<\/strong> from the context menu that appears, as you did earlier.<\/li>\n<li>Type, or cut and paste, the following into the <strong>Formula <\/strong>bar:<\/li>\n<\/ol>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">c_RATE = \r\nRATE(\r\n   Loans[Nper],\r\n      Loans[Pmt],\r\n          Loans[Pv],\r\n             Loans[Fv],\r\n          Loans[Type],\r\n       Loans[Rate % - Annual]\/12)<\/pre>\n<p>The calculation appears as shown in the <strong>Formula<\/strong> bar:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"525\" height=\"298\" class=\"wp-image-88320\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-121.png\" \/><\/p>\n<p><strong>Illustration 13: Calculation Containing the RATE() Function \u2026<\/strong><\/p>\n<ol start=\"4\">\n<li>Click the checkmark to the left of the <strong>Formula <\/strong>bar to check and commit the calculation, and to create the new calculated column.<\/li>\n<\/ol>\n<p>The calculation <strong>c_RATE <\/strong>appears in the <strong>Loans<\/strong> table in the <strong>Fields<\/strong> pane.<\/p>\n<ol start=\"5\">\n<li>With the new calculated column <strong>c_RATE <\/strong>selected in the <strong>Fields<\/strong> pane, make the following <strong>Format<\/strong> settings underneath the main menu:\n<ul>\n<li>Percentage (%)<\/li>\n<li>2 decimal places<\/li>\n<li>Don\u2019t summarize<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<ol start=\"6\">\n<li>Select the <strong>DAX Finance Calculations (&#8220;c_&#8221;)<\/strong> table visualization, once again, and add the new <strong>c_RATE <\/strong>calculation to the <strong>Values<\/strong> section of the <strong>Fields<\/strong> tab for the table, underneath the existing <strong>c_PMT <\/strong>calculation you added in the prior practice exercise.<\/li>\n<\/ol>\n<p>The <strong>Rate<\/strong> value within the <strong>Loan Details<\/strong> table visualization serves as a means of checking the output accuracy of the new calculation, as shown in the current results.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"400\" class=\"wp-image-88321\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-122.png\" \/><\/p>\n<p><strong>Illustration 14: Verifying Accuracy of Value Returned via the New Calculation<\/strong><\/p>\n<p>Again, you can conclude that you\u2019ve successfully assembled a calculation to demonstrate the operation of the DAX <code>RATE()<\/code> financial function if your results agree to those above.<\/p>\n<p>Next, you\u2019ll get some exposure to the DAX <code>NPER()<\/code> function.<\/p>\n<h2>DAX Financial Function: NPER()<\/h2>\n<p>According to the <em>Data<\/em> <em>Analysis Expressions (DAX) Reference<\/em>, the DAX <code>NPER()<\/code> financial function \u201creturns the number of periods for an investment based on periodic, constant payments and a constant interest rate.\u201d <code>NPER()<\/code> is often used, within amortization or savings scenarios, to calculate points in time in which interest is earned\/accrued. In this specific use case, it will calculate the number of periods to pay off a given loan under these circumstances \u2013 the total number of periods (months, quarters, years, etc.) over which the loan is to be paid.<\/p>\n<p>You\u2019ll see how <code>NPER()<\/code> works with the loan data in your Power BI model in the next section. Again, you\u2019ll be working under the assumption that the parameter you seek, the number of periods, is the only unknown variable.<\/p>\n<h3>Syntax<\/h3>\n<p>Syntactically, the parameters to provide are specified within the parentheses to the right of <code>NPER<\/code> as shown:<\/p>\n<pre class=\"lang:tsql decode:true \">NPER(&lt;rate&gt;, &lt;pmt&gt;, &lt;pv&gt;[, &lt;fv&gt;[, &lt;type&gt;]])<\/pre>\n<p>The arguments are explained in the section named <strong>Shared Parameters for DAX Financial Functions in Part 1<\/strong> above.<\/p>\n<h3>Return Value and Further Remarks<\/h3>\n<p><code>NPER()<\/code> returns the number of periods in the life of a loan\/investment.<\/p>\n<p>Next, you\u2019ll put <code>NPER()<\/code> to work upon the loan dataset within your Power BI model.<\/p>\n<h3>Practice<\/h3>\n<p>Once inside the Power BI model again, you\u2019ll create a calculation in the <strong>Loans<\/strong> table that uses the <code>NPER()<\/code> function. The parameters, once again, will be sourced from the <strong>Loans<\/strong> table within the model. You\u2019ll also be able to validate your results with <code>NPER()<\/code> instantly, via comparison of the calculated <code>NPER()<\/code> values to those in your <strong>Loan Details<\/strong> table visualization.<\/p>\n<p>Employ the DAX <strong>NPER()<\/strong> Function to Return the Number of Periods for Each of a Group of Loans<\/p>\n<p>Take the following steps to create another calculation, this time to return the respective number of periods in the lives of each of the loans in your sample dataset.<\/p>\n<ol>\n<li>From the <strong>Report<\/strong> view, right-click the <strong>Loans<\/strong> table in the <strong>Fields <\/strong>pane of the model, once again.<\/li>\n<li>Select <strong>New column<\/strong> from the context menu that appears, as before.<\/li>\n<li>Type, or cut and paste, the following into the <strong>Formula <\/strong>bar:<\/li>\n<\/ol>\n<pre class=\"lang:tsql decode:true\">c_NPER =\r\n\r\nNPER(\r\n\r\n   Loans[Rate],\r\n\r\n   Loans[Pmt],\r\n\r\n   Loans[Pv],\r\n\r\n   Loans[Fv],\r\n\r\n   Loans[Type]\r\n)<\/pre>\n<p>&nbsp;<\/p>\n<p>The calculation appears as shown in the <strong>Formula<\/strong> bar:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"524\" height=\"330\" class=\"wp-image-88322\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-123.png\" \/><\/p>\n<p><strong>Illustration 15: Calculation Containing the NPER() Function \u2026<\/strong><\/p>\n<ol start=\"4\">\n<li>Click the checkmark to the left of the <strong>Formula <\/strong>bar to check and commit the calculation, and to create the new calculated column.<\/li>\n<\/ol>\n<p>The calculation <strong>c_NPER <\/strong>appears in the <strong>Loans<\/strong> table in the <strong>Fields<\/strong> pane.<\/p>\n<ol start=\"5\">\n<li>With the new calculated column <strong>c_NPER <\/strong>selected in the <strong>Fields<\/strong> pane, make the following <strong>Format<\/strong> settings underneath the main menu:\n<ul>\n<li>Decimal number<\/li>\n<li>0 decimal places<\/li>\n<li>Don\u2019t summarize<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<ol start=\"6\">\n<li>Select the <strong>DAX Finance Calculations (&#8220;c_&#8221;)<\/strong> table visualization, once again, and add the new <strong>c_NPER <\/strong>calculation to the <strong>Values<\/strong> section of the <strong>Fields<\/strong> tab for the table, underneath the existing <strong>c_RATE <\/strong>calculation.<\/li>\n<\/ol>\n<p>The <strong>NPer<\/strong> value within the <strong>Loan Details<\/strong> table visualization serves as a means of checking the output accuracy of the new calculation, as shown in the current results.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"424\" class=\"wp-image-88323\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-124.png\" \/><\/p>\n<p><strong>Illustration 16: Verifying Accuracy of Value Returned via the New Calculation<\/strong><\/p>\n<p>Each value displayed within your <strong>Loan Details<\/strong> table visualization should agree, once again, with that of the corresponding calculation in the <strong>DAX Finance<\/strong> <strong>Calculations<\/strong> table above.<\/p>\n<p>In the next section, you\u2019ll get some exposure to the DAX <code>PV()<\/code> function.<\/p>\n<h2>DAX Financial Function: PV()<\/h2>\n<p>According to the <em>Data<\/em> <em>Analysis Expressions (DAX) Reference<\/em>, the DAX <code>PV()<\/code> financial function \u201ccalculates the present value of a loan or an investment, based on a constant interest rate.\u201d You have probably encountered the concept of present value (variously \u2013 and sometimes inconsistently &#8211; called \u201cprincipal amount,\u201d \u201cpresent discounted value,\u201d or even \u201cdiscounted value\u201d) in the business environment, or from exposure in finance or economics elsewhere.<\/p>\n<p><code>PV()<\/code> can be used with either a future value (say, an investment goal) or periodic, constant payments (as in the case of a mortgage loan). In the case of an investment<strong>, <\/strong><code>PV()<\/code> returns the initial investment or deposit. For a loan, it calculates the amount one is borrowing. Because it represents the value of an anticipated income stream as of the date of valuation, the present value is almost always less than the future value (due to the time value\/interest earning potential of money).<\/p>\n<h3>Syntax<\/h3>\n<p>Syntactically, the parameters you provide are specified within the parentheses to the right of <strong>PV <\/strong>as shown:<\/p>\n<p>PV(&lt;rate&gt;, &lt;nper&gt;, &lt;pmt&gt;[, &lt;fv&gt;[, &lt;type&gt;]])<\/p>\n<p>The arguments are explained in the section named <strong>Shared Parameters for DAX Financial Functions in Part 1<\/strong> above.<\/p>\n<h3>Return Value<\/h3>\n<p><code>PV()<\/code> returns the present value of a loan or investment.<\/p>\n<p>Time to get some hands-on exposure to <code>PV()<\/code> in Power BI with the steps in the <strong>Practice<\/strong> section below.<\/p>\n<h3>Practice<\/h3>\n<p>Returning, once again, to the Power BI model you have prepared, you\u2019ll create a calculation in the <strong>Loans<\/strong> table that uses the <code>PV()<\/code> function. As you\u2019ve done with the preceding DAX financial functions, you\u2019ll source the parameters for <code>PV()<\/code> from the <strong>Loans<\/strong> table within the model. Once again, the \u201canswer\u201d to be expected via the calculation you create already exists in static form in the table within the model. You can validate the results you obtain through easy comparison to the <strong>Pv<\/strong> column as displayed in the <strong>Loan Details<\/strong> table visualization that you created in your preparation steps.<\/p>\n<p>Employ the DAX <strong>PV()<\/strong> Function to Return the Present Value for a Group of Loans<\/p>\n<p>Create a calculation to return the respective present values for the loans in your sample dataset via the following steps.<\/p>\n<ol>\n<li>From the <strong>Report<\/strong> view, right-click the <strong>Loans<\/strong> table in the <strong>Fields <\/strong>pane of the model.<\/li>\n<li>Select <strong>New column<\/strong> from the context menu that appears.<\/li>\n<li>Type, or cut and paste, the following into the <strong>Formula <\/strong>bar:<\/li>\n<\/ol>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">C_PV = \r\nPV(\r\n   Loans[Rate], \r\n      Loans[Nper], \r\n         Loans[Pmt], \r\n      Loans[Fv], \r\n   Loans[Type])<\/pre>\n<p>The calculation appears as shown in the <strong>Formula<\/strong> bar:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"525\" height=\"267\" class=\"wp-image-88324\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-125.png\" \/><\/p>\n<p><strong>Illustration 17: Calculation Containing the PV() Function \u2026<\/strong><\/p>\n<ol start=\"4\">\n<li>Click the checkmark to the left of the <strong>Formula <\/strong>bar to check and commit the calculation, and to create the new calculated column.<\/li>\n<\/ol>\n<p>The calculation <strong>c_PV <\/strong>appears in the <strong>Loans<\/strong> table in the <strong>Fields<\/strong> pane.<\/p>\n<ol start=\"5\">\n<li>With the new calculated column <strong>c_PV <\/strong>selected in the <strong>Fields<\/strong> pane, make the following <strong>Format<\/strong> settings underneath the main menu:\n<ul>\n<li>Currency ($)<\/li>\n<li>0 decimal places<\/li>\n<li>Don\u2019t summarize<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<ol start=\"6\">\n<li>With the <strong>DAX Finance Calculations (&#8220;c_&#8221;)<\/strong> table visualization selected, once again, add the new <strong>c_PV <\/strong>calculation to the <strong>Values<\/strong> section of the <strong>Fields<\/strong> tab for the table, underneath the existing <strong>c_NPER <\/strong>calculation.<\/li>\n<\/ol>\n<p>The <strong>Pv<\/strong> value within the <strong>Loan Details<\/strong> table visualization serves as a means of checking the output accuracy of the new calculation, as shown in the current results.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"937\" height=\"408\" class=\"wp-image-88325\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-126.png\" \/><\/p>\n<p><strong>Illustration 18: Verifying Accuracy of Value Returned via the New Calculation<\/strong><\/p>\n<p>Again, verify the results you\u2019ve generated to the corresponding values in the Loan Details table to ensure accuracy.<\/p>\n<p>Finally, you\u2019ll wrap the first part of \u201cIntroduction to DAX financial Functions\u201d with an overview and demonstration of the DAX <code>FV()<\/code> function.<\/p>\n<h2>DAX Financial Function: FV()<\/h2>\n<p>According to the <em>Data<\/em> <em>Analysis Expressions (DAX) Reference<\/em>, the DAX <code>FV()<\/code> financial function \u201ccalculates the future value of an investment based on a constant interest rate.\u201d <code>FV()<\/code> can be used to calculate future value in scenarios involving periodic, constant payments (as you\u2019ve encountered within the loan-based data you\u2019ve worked with throughout this article), and\/or single \u201clump sum\u201d payments. It reflects the principal plus interest received or paid.<\/p>\n<p>Conceptually, future value is the value of an asset at a specific date in the future \u2013 what, for instance, a given sum of money today is worth at a specified future time, assuming a certain rate of return\/interest rate. It is typically a consideration that arises in \u201ctime value of money\u201d concerns, where it is easy to see the value of being able to determine how much money one will have at a future point, given, say, a starting balance, regular payments, and a compounding interest rate. Other factors, such as adjustments for inflation, are not taken into consideration within calculations one undertakes solely with <code>FV()<\/code>.<\/p>\n<p>You\u2019ll take a look at how <code>FV()<\/code> works with the loan data you\u2019ve have used within the other financial functions. You\u2019ll take up your practice with <code>FV()<\/code> as if the future value itself is the only unknown variable, and use the <code>FV()<\/code> function to calculate the future value, with the associated stated parameters that are assigned for the respective loan.<\/p>\n<h3>Syntax<\/h3>\n<p>Syntactically, the parameters to provide are specified within the parentheses to the right of <strong>FV<\/strong> as shown:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">FV(&lt;rate&gt;, &lt;nper&gt;, &lt;pmt&gt;[, &lt;pv&gt;[, &lt;type&gt;]])<\/pre>\n<p>The arguments are explained in the section named <strong>Shared Parameters for DAX Financial Functions in Part 1<\/strong> above.<\/p>\n<h3>Return Value and Further Remarks<\/h3>\n<p><code>FV()<\/code> returns the future value of an investment.<\/p>\n<p>Keep in mind that the units for specifying <strong>Rate <\/strong>and <strong>Nper<\/strong> must be consistent.<\/p>\n<p>Example: An individual makes <em>monthly <\/em>payments on a 20-year loan, the annual interest rate for which is 6 percent. In this case, you would supply a <strong>Rate<\/strong> of .06\/12, and 20*12 (or 240) for <strong>Nper<\/strong>.<\/p>\n<p>Now for some hands-on exposure to <code>FV()<\/code> in Power BI.<\/p>\n<h3>Practice<\/h3>\n<p>You\u2019ll create a calculation in the <strong>Loans<\/strong> table once again, this time containing the <strong>FV()<\/strong> function and again sourcing the <strong>Loans<\/strong> table for the parameters.<\/p>\n<p>Employ the DAX <strong>FV()<\/strong> Function to Return the Future Value for a Group of Loans<\/p>\n<p>Create a calculation to return the future values for each of the loans in the sample dataset via the following steps.<\/p>\n<ol>\n<li>From the <strong>Report<\/strong> view, right-click the <strong>Loans<\/strong> table in the <strong>Fields <\/strong>pane of the model, once again.<\/li>\n<li>Select <strong>New column<\/strong> from the context menu once more.<\/li>\n<li>Type, or cut and paste, the following into the <strong>Formula <\/strong>bar:<\/li>\n<\/ol>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">c_FV = \r\nFV(\r\n   Loans[Rate],\r\n      Loans[Nper],\r\n          Loans[Pmt],\r\n      Loans[Pv],\r\n   Loans[Type])<\/pre>\n<p>The calculation appears as shown in the <strong>Formula<\/strong> bar:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"525\" height=\"329\" class=\"wp-image-88326\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/09\/word-image-127.png\" \/><\/p>\n<p><strong>Illustration 19: Calculation Containing the FV() Function \u2026<\/strong><\/p>\n<ol start=\"4\">\n<li>Click the checkmark to the left of the <strong>Formula <\/strong>bar to check and commit the calculation, and to create the new calculated column.<\/li>\n<\/ol>\n<p>The calculation <strong>c_FV <\/strong>appears in the <strong>Loans<\/strong> table in the <strong>Fields<\/strong> pane.<\/p>\n<ol start=\"5\">\n<li>With the new calculated column <strong>c_FV <\/strong>selected in the <strong>Fields<\/strong> pane, make the following <strong>Format<\/strong> settings underneath the main menu:\n<ul>\n<li>Currency ($)<\/li>\n<li>0 decimal places<\/li>\n<li>Don\u2019t summarize<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<ol start=\"6\">\n<li>Select the <strong>DAX Finance Calculations (&#8220;c_&#8221;)<\/strong> table visualization, once again, and add the new <strong>c_FV <\/strong>calculation to the <strong>Values<\/strong> section of the <strong>Fields<\/strong> tab for the table, underneath the existing <strong>c_PV <\/strong>calculation.<\/li>\n<\/ol>\n<p>Future Value is not represented within the <strong>Loan Details<\/strong> table, but you can conclude that the output accuracy of the new <strong>c_Fv<\/strong> calculation is accurate, as the \u201cfuture value\u201d of a loan fully paid off, at the rate and number of periods specified, would near-zero (there would likely be minor rounding due to decimalization, etc.).<\/p>\n<h2>Summary<\/h2>\n<p>In this, <strong>Part 1<\/strong> of an article series introducing the new DAX financial functions, I introduced a popular subgroup of those functions that deal largely with loans or investments. My objective was to examine how each function can be employed, within Power BI, to support analysis and reporting of the sort one might accomplish using Microsoft Excel. For each function you explored, you learned its purpose, then examined the DAX syntax involved in its use. Moreover, you gained exposure, via an illustrative example for each function, to the use of the respective function with practice loan data, and then confirmed your understanding of the results you had obtained with each function.<\/p>\n<p>In <strong>Part 2<\/strong> of this series, I will introduce a second group of the new DAX financial functions, this time the ever-popular Depreciation functions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson kicks off a series to introduce the DAX financial functions. In this article, he exposes five functions that are popular in the calculation of loans and other annuities. Those accustomed to these functions within Excel will find the syntax, uses and operation of the functions familiar within the Power BI environment.&hellip;<\/p>\n","protected":false},"author":221883,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528,159166],"tags":[124831,124833,5872,124822,4643,124828,6045,124827,124834,124825,124823,101611,5871,124826,124824,4476,5134,124832,124836,124830,124829,124835],"coauthors":[48707],"class_list":["post-88307","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","category-powerbi","tag-aggregation","tag-calculation","tag-dax","tag-desktop","tag-excel","tag-format","tag-function","tag-fv","tag-measure","tag-nper","tag-pmt","tag-power-bi","tag-powerpivot","tag-pv","tag-rate","tag-report","tag-sql-prompt","tag-summarize","tag-table","tag-total","tag-visual","tag-visualization"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88307","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221883"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=88307"}],"version-history":[{"count":26,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88307\/revisions"}],"predecessor-version":[{"id":89423,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/88307\/revisions\/89423"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=88307"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=88307"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=88307"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=88307"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}