Developing a financial KPI report is probably going to be the most important report you create for a business. It will be used by management and senior management to monitor and gain insight into the performance of a business and essentially track if they are on target or not on their KPI (which they will be potentially bonused or fired!).
To demonstrate the development of a financial KPI report, we will use data from the Contoso Retail database, which is a fictitious database created by Microsoft.
Instructions for installing the database on your local machine are here
Once you have installed the database you can connect Power BI to it and start pulling in some tables.
The first table we work with is the FactSales Table. This is the main table containing all the sales transactions for Contoso.
You might wonder how to know which tables to pull in and this is all part of the developer’s job. With a bit of luck, you will have other developers who can guide you through how the database is organized, but sometimes you will be left to figure it all out for yourself and this can be a lengthy process in learning and understanding. Talking to subject matter experts on areas of the data will also be of great assistance to you as you develop your knowledge of how everything fits together.
To see how I explore the Contoso Retail Datawarehouse – see this article
To import the FactSales table from the Contoso Retail database on your SQL server installation. Select Get Data > SQL Server.
You will then need to enter the Server information. I’m using SQL Server developer edition installed on my local machine. For me, I just enter ‘localhost’ as the server name. If you have a server on the network, ask your IT guy for the server name. Also, you can enter the database name here ‘ContosoRetailDW’. Persevere, you are nearly there.
Once you have connected, you will see a list of all the tables and views in the Contoso Database.
Select the tables you want to import and click ‘Transform’ to load the table into Power Query or ‘Load’, to just load the table directly into the model without bringing up the Power Query window.
It’s best practice to transform the data first before working on it, so open the data in Power Query. If you’re not there already, hit the Transform data button from the Home ribbon.
The FactSales table is in pretty clean shape, but if you need to transform the dates into date form or add additional columns, pivot the table, and many other options you can do it here in Power Query.
If the tables were loaded into dataflows on the Power BI service, you can work with Power Query in the service, so the table is transformed for anyone using the dataflow and not just in the report you have built.
Once you’ve finished any transformation you wanted, you can close the Power Query window and return to the main view.
In addition to the FactSales table, as we want to create measures using Time Intelligence in this demonstration, we also want to use a Date table, to join to our FactSales table.
A date table is a key table in most Power BI reports and it’s good to include it as standard in your Power BI template.
Instructions for creating a Date table with DAX can be found here.
Here’s what a basic date table looks like. It can have a lot of columns, including a separate set of columns related to the financial year.
Once you have your Date table available, we can join the Date table to the FactSales table using the date field as the Key in each. Open up the Modelling view.
You can then join the tables by selecting the Date field on the date table and dragging it to the FactSales DateKey. Power BI will automatically assign the join type. In this case, One (on the Date table) to Many (on the FactSales table), because Date is a unique field on the Date table, but non-unique on the FactSales table.
Now these basic elements of the model are set up, we can start creating the measures to use in the Financial Report, starting with the most important to any business, the sales or turnover numbers.
Calculating the Sales Amount for YTD, QTD, MTD
1. The first measure we create is Revenue Year to Date. We use the calculate function to filter the Sales Amount by the DatesYTD function. The DatesYTD Function takes the date from the date table
Sales YTD = CALCULATE(SUM(FactSales[SalesAmount]) , DATESYTD(‘Date'[Date]))
2. Next, there is the Quarter to Date measure we can create.
Sales QTD = CALCULATE(SUM(FactSales[SalesAmount]) , DATESQTD(‘Date'[Date]))
3 . Then, we create a revenue Month to Date measure, in a similar format, but with the DATESMTD Function
Sales MTD = CALCULATE(SUM(FactSales[SalesAmount]) , DATESMTD(‘Date'[Date]))
We can then use the measures in cards in the Power BI report.
Calculating Sales Measures for the Same Period in the Previous Year (YTD, QTD, MTD)
Once we have measures for the currently select year, we typically will want to display performance against the previous year, to see how the business is growing Year on Year (YoY)
Here we can use the following DAX Formula:
4. For the same period last year – year-to-date, we wrap the date filter DATESYTD, with a second filter SAMEPERIODLASTYEAR(), to get the YTD revenue for the same period of the previous year.
Revenue LY YTD =
CALCULATE(SUM(FactSales[SalesAmount]) , SAMEPERIODLASTYEAR(DATESYTD(‘Date'[Date])))
5. For the same quarter to date last year, we can use the following:
Revenue LY QTD =
CALCULATE(SUM(FactSales[SalesAmount]) , SAMEPERIODLASTYEAR(DATESQTD(‘Date'[Date])))
6. And lastly, for the same period last year month to date, we can do this:
Revenue LY MTD =
CALCULATE(SUM(FactSales[SalesAmount]) , SAMEPERIODLASTYEAR(DATESMTD(‘Date'[Date])))
We can test the working of the calculations on Power BI, using filters on the Date table and the 6 measures we created on the cards below.
Calculating Sales Variance Year on Year YTD, QTD, MTD
We can now create some simple variance measures, for calculating the differences from the period selected versus the previous year.
The following 3 measures just take the difference between the measures taken above for the current year vs. the previous year.
Revenue VAR YTD vs. Last Year = [Revenue YTD]-[Revenue LY YTD]
Revenue VAR QTD vs. Last Year = [Revenue QTD]-[Revenue LY QTD]
Revenue VAR MTD vs. Last Year = [Revenue MTD]-[Revenue LY MTD]
We can now display the variances below the current and previous years’ calculations.
Now we have these initial measures we can develop additional measures for other KPI, such as Orders, Quantity, Average Order Value (AOV).
The first measure created was for orders YTD. Here we use a similar time intelligence formula as for sales, but in the format:
Orders YTD = CALCULATE(DISTINCTCOUNT(FactSales[SalesKey]), DATESYTD(‘Date'[Date]))
And from there we can create the Average Order Value (AOV) Year to Date (YTD) measure of:
AOV YTD = [Sales YTD]/[Orders YTD]
The next measure to add is quantity, we can create the actual quantity:
Quantity = SUM(FactSales[SalesQuantity])
and the YTD quantity of:
Quantity YTD = CALCULATE(SUM(FactSales[SalesQuantity]), DATESYTD(‘Date'[Date]))
As we can see from the report, the quantity YTD for 2008 is 16.5M vs. Orders of 1.1M.
Once we have the measures we can start building the final KPI report, using only the most relevant measures in a summary report. It often helps to have a more basic summary report in addition to a more detailed report for reference, as different report audiences will require different levels of detail. In a meeting of twenty people, delivering the top-level figures will often be sufficient, to get the key message across. The report below shows just the top-level KPI at the top of each measure set into a separate card.
Below the measures are displayed as trends over time, using bar charts and on the first graph we compare sales against the previous years.
From our exploratory analysis of the Contoso database, we learned that the StoreType in the DimStore table, could be used to break the data down into four main sales channels. We can import the store table from Power Query, just as we did for the FactSales table, and join it to the FactSales table
In the Model view, we can then join the DimStore table to the FactSales table and join them using the StoreKey.
Once we have the model set up in this way we can start to drill down into the top-line KPI and break them down by Catalog, Online, Reseller, and Store channels. Here we simply add a slicer for Store Type to our report page, but of course, many options are available. It would be good to view sales by StoreType side by side for example.
That’s it for now. These are the structural elements of getting started building a Financial KPI report. The report can branch out into many different areas. It’s easy to get lost, the best practice is to listen to the person that requested the report and address all there requirements as best as possible. When the report starts being used, it will inevitably require further development as more questions are raised, that people want answers to.
Leave a Reply