Power BI use case - Developing a Personal Finance Calculator

Problem

Power BI can be used for many purposes and in this tip we will look at how to use Power BI to build a personal expenses score card.

Solution

Almost all banks have the facility to download bank statements in a CSV file format. In this tip, I will help you build a Personal Finance Tracker using Power BI. We will also create KPI’s and key indicators to understand how to track expenses and income.

Bank Statements

The image below represents the bank statement in a CSV format. This file has the following columns:

TransactionDate – The date in which the actual transaction happenedTransactionType – The type of transaction denotes the mode of transaction (Debit,Transfer,Standing Order, etc.)TransactionDescription – Full details of the transactionDebit Amount – Amount debited in case of expenseCredit Amount – Amount credited in case of income

Import File into Power BI Desktop

Now let’s import the bank statement file into Power BI using Get Data > Text/CSV as shown below.

Power BI has correctly identified the columns.

Now let’s edit the query to modify the properties and name this “Transactions” as shown below.

Now Close & Apply the query to save the changes.

Creating Date Table with Attributes

Now let’s create a date table using calculated tables. This previous tip details the creation of a Date table with many attributes. As per the previous tip, the date table has been created with the DAX expression below.

Now the date table can be extended with attributes such as Month, Quarter and Year.

In addition, a relationship has been created between the transaction table and the date dimension table as shown below.

Create Report

Now let’s create a simple report to list all the transactions.  A data table visualization has been chosen to represent the transactions.  As the relationship has been created between the Date table and the Transactions, now we can make use of the additional columns defined in the Date table for reporting.

The following columns have been chosen for this report.

Date (DimDate table)CreditAmount (Transactions)DebitAmount (Transactions)TransactionType (Transactions)TransactionDescription (Transactions)

Source