Financial Statements in Power BI Dashboards- V2!

Nearly two years ago we released our most successful blog post and corresponding video to date - Financial Statements in Power BI!

Since that time we have updated the way we build these in Microsoft Power BI and witnessed the joy of many CFOs. My colleague Chris has been nailed down a new methods that riffs off the original from two years ago. It eliminates the need for COMPLEX DAX and with speedy, cute measures that can be written in moments for even better data visualization.

Advantages include:

• 1) Front-loaded projects with increased sustainability

• 2) Simpler DAX, and…

• 3) Higher level of PBI precision.

Watch me, or follow along…

At the bottom of this page, you should find a video to view how it all comes together. Alternatively, you can follow along step by step in the blog below… enjoy!

The Business Analytics Data from Excel

Our data set has three sheets.

• Income Statement (IS) Structure

• Budgets

• Actuals

IS Structure:

This data-set describes how the data is displayed.

ORDER helps keep.. well the order. Once this data-set hits PowerBI, It makes sure that the data is on the correct sort order.

ACCOUNTS is what maps an account to an order number. This is labour intensive the when you first build the report, but cuts down on complexity.

TYPENUM allows us to ‘collapse’ groups of rows. For example, we will be able to only show rows 9, 18, 19, 23, 24 from the range 8 - 25 when we choose level 2 later on.

FORMATTEDTITLE is just a visual interpretation of what the Order Column actually represents.

Actuals:

ACCOUNTKEY is just the account that the line represents

BRANCHKEY is not used in this example, but allows for Consolidations

DOCDATE is what day the Actual Transaction happened on

AMOUNT is the Actual

Budgets:

ACCOUNTKEY is just the account that the line represents

BRANCHKEY is not used in this example, but allows for Consolidations

CUSTOM is what day/month the Budget is mapped to

AMOUNT is the Budget

Loading the Dataset onto the Power BI Dashboard

The magic happens when we add in our relationships. By loading IS Structure AND a manipulated version of IS Structure ( IS Structure Mapping ), we can seamlessly join our actuals/budgets to our mapping file.

• Load IS Structure

• Load Actuals

• Load Budgets

Finally load in the Mapping Data set. The key to full mappings is in the ‘Split by Delimiter’ and ‘Unpivot Other Columns’ functions.

• Load IS Structure again

• Only keep Order and Accounts, delete all other columns

• Split by Delimiter, make sure to specify that you wish to split all instances of your chosen delimiter

• Select Order and then Unpivot OTHER columns

• Clean data as necessary

I would really recommend watching the video for this part, just from 5:45 to 8:20 ish if you are unsure of what you are doing.

After loading the data , you should map the data-sets like the image

• Is Structure[Order] -> Is Structure Mappings[Order]

• Is Structure Mappings[Account] -> Actuals[Account]

• Is Structure Mappings[Account] -> Budgets[Account]

Or just see image…

The Dax

I like to have base measures and display measures.

Base measures contain Core Logic, Display measures take that core logic and expand on it in niche ways. We’re not saying is the BEST way, but this is a good starting point.

Examples might be:

• Base: Ice Cream

• Display1: Mint

• Display2: Chocolate

or:

• Base: Actuals

• Display: MTD Actuals

• Display: YTD Actuals

So this particular report has the following measures:

• SP Base Measure

• SP Display

• BUD SP Base Measure

• BUD SP Display

• SP BUD Variance %

Compiling the Power BI Dashboard

Drop in a matrix and add the following to the rows:

• Order from the Is structure

• Formatted Title From Is structure

To the columns add:

• Dates[Year]

• Dates[Month]

To the values add:

• SP Display

• BUD SP Display

• SP BUD Variance%

• A Measure that = BLANK()

As for Formatting:

• Remove all word wrap

• Turn off stepped layout

• Remove all Subtotals

• On the drop down for Order, click include items with no data

In the end you should have something similar to the image above.

By spending time up-front and building comprehensive Income Statement and Balance Sheets you could potentially eliminate your entire financial reporting process.

Imagine a world where you no longer manually assemble your financials every month, cell by cell, page by page. Think of the business intelligence you'll get out of your data model. That all becomes irrelevant when you package your financial data in PowerBI. Slicers replace duplicated, time consuming tabs and your IS structure page ‘builds’ your report for you. That’s a pretty good deal.

We have seen this process completely change how some finance departments function. All around the world we have see this base method take a monthly, 3+ week long financial reconciliation and assembly process and turn it in to a ‘3 clicks and you’re looking at the latest data’ process. It’s turned some of our partners in to long-term fans of FreshBI.

The methods above are the simplest form of what can be done now. You can imagine that this report can be finessed to contain YTD numbers, off the wall KPIs and even prettier front-ends. That’s what we love about PowerBI.

Our Latest Blogs

About FreshBI

Operating throughout USA and Canada, FreshBI is a boutique Microsoft Partner using Power BI to unlock the value trapped in your data so that your business can succeed.