How do you track your monthly expense? Well, most of the people don’t track monthly expense. Few do that by using diary, using Microsoft Excel or by using smartphone apps. Whatever be the case, one thing is sure that tracking your expense is one of the key factors in making your budget work for you. If you don’t know how much you have spent every month, you cannot control your expense and you will end up spending more.
In short, it is essential to track your monthly expense to gain control over it. There are multiple ways to track monthly expense. In this post, I will share a simple method to track your monthly and yearly expense using Power BI.
What is Power BI?
Power BI is a powerful visualization and analytics tool by Microsoft. This tool is freely downloaded from Microsoft Site. We have already discussed Power BI features and benefits in our previous post. Apart from that, we have also seen How to use Power BI for Stock Market Analysis and Mutual Fund Portfolio Analysis using Power BI. In this post, we will see how to track monthly expense using Power BI.
Personal Finance Analysis using Power BI – Budget Vs Actual
The first step towards making own Monthly Expense tracker is creating a source file. In the example given below, we will be using Microsoft Excel as an input file. The file which will be using contains column namely date, description, debit, credit, category and budget. You can also bank account statement available in excel format as an input.
Once you have the required data in excel you need to press the “Get Data” button and provide Excel file which you are using as a data source to Power BI.
Once data is imported, you can start making a dashboard. In the example given below, I have used visualization of Expense Category Analysis, Budget Vs Actual, Total Credit and Debit amount & Latest and oldest expense date. I have also given a filter for month wise expense analysis.
Make Expense Tracker Dashboard
To get Latest Expense date click on New Measure and find out max by writing following query.
- Latest Expense Date = Max(‘Expense'[Date])
Once you find this detail click on Card visualization and you will be able to see the latest expense date. You can change format and change fonts as per your requirement.
Second is getting the oldest expense date. To get this click on New Measure and find out oldest expense by writing the following query.
- Oldest Expense Date = Min(‘Expense'[Date])
After getting Oldest Expense Date to create visualization and change font and format as per your requirement.
To create Expense Category Analysis, select a debit amount, category and go for Pie Chart as visualization. You will be able to see all the expenses category wise. If you want to get Budget v/s Actual data you need to select a date, debit amount and budget and apply Clustered-column-chart.
You can also do month wise analysis by applying month wise filter.
Drill Through and Analysis Facility
You can drill through visualization by doing right click and clicking on “See Records” you will be able to see all records associated with visualization.
You can also get an analysis facility in Power BI. You just need to right click and select “Analyse”. It will show option such as Explain the Increase and find where distribution is different.
Benefits of Power BI Expense Dashboard
- Detail Analytical View of Expenses
- Deep-dive by going through details
- Inbuilt Analysis Facility
- Easy Decision making
I hope you liked the method shown above to do Personal Finance Analysis using Power BI. Do share your views in the comment section.
