From the course: Advanced Power BI: DAX Language, Formulas, and Calculations

Introducing the course project - Power BI Tutorial

From the course: Advanced Power BI: DAX Language, Formulas, and Calculations

Start my 1-month free trial Buy for my team

Introducing the course project

“

- [Instructor] For this course, we're going to create a Power BI data model that uses DAX calculations to build out a loan model based on mortgage rates. Mortgages are paid off over different periods of time. A 30-year loan is one of the most common mortgage durations out there, paid every month as a fixed interest rate for 30 years. We're going to use the interest rates from the Federal Reserve Economic Data, or FRED, in our model calculations, including the 30-year mortgage rate we see on the page. There are other loan durations as well, like the 15-year fixed mortgage rate, which also comes from FRED. The 15-year mortgage rate starts about 2 decades later in terms of tracking the interest rate than the 30-year rate. It's also typically lower than the 30-year mortgage interest rate, because we're loaning money over a shorter period of time. In Power BI, I already set up connections to these FRED data sources for both 15-year and the 30-year fixed mortgage interest rates through the Power Query Editor. In this course, we're going to use the DAX language to build a Power BI model for these loan calculations. We'll also briefly discuss how to use the dynamic visualization capabilities of Power BI to communicate how the loan model works to a wider business audience. On a high level, here's how a loan works: At time zero, we borrow a given amount of money. We then agree to pay back the loan in payments over a given time period. Like in this case, we have five years. For our model, we're going to say the loan payments are even and pay it at a regular cadence. If we compare the total loan payments to the total loan amount taken out at time zero, the total payments are higher. This is because when we make payments, we're not only paying back the principal of the loan we took out at the beginning, but also the interest on this loan, which we can use the 15-year and 30-year average rates to determine what the loan interest will be. The interest paid isn't the same for each payment period though, but rather it decreases over time as the loan balance decreases over the duration of the life of the loan. This subsequently means, we're paying more of the payment toward the principal as we progress through the life of the loan. Some of the key calculations we'll do in the loan model include converting an annual interest rate into a monthly one, where i represents the annual interest rate. The level payment amount. The loan balance at each period in the loan. The interest paid for each period. And the principal paid for each period. To keep track of the DAX formulas in this course, I'm also going to number them for the individual tables for Model 1 and Model 2. Because the order of fields in Power BI gets alphabetized by text characters starting at zero, we'll then denote the order of the measures calculated within each model using the sequence zero-one zero-two and so on as we build out the loan models in Power BI.

Contents