This Chapter provides some basic foundations for anyone wanting to create financial reports in Power BI. The accounting first principles lesson covers the source data that we need and  generic calculations such as movement and balance. In lesson 4, we look at the essential differences between Excel and Power BI, in the way that calculations are performed. Lessons 5 and 6 take this to a deeper level, exploring the two "evaluation contexts" that determine what data are visible to a calculation.

Accounting demo data

Introduction and objectives

Accounting first principles for reporting

Excel v Power BI fundamental differences

The Power BI Filter Context

The Power BI Row Context

Summary, questions and further study

Foundations quiz

Power BI Files

PowerPoint files

Foundations - essential accounting and Power BI concepts

At first glance, date-driven financial reporting is very simple, but there are many subtleties. In this chapter you will learn how to use Dates Measures to create highly-intuitive date range selection in your report, coupled with the ultimate flexibility that can be delivered with Power BI.

Challenges with dates and financial reporting

Why & how to create and use a 'Dates' table

Connect your new Dates table to your report

Why I never use DAX Time Intelligence functions

Using disconnected tables & dates measures to provide simple, flexible, intuitive date filters

Fixing an error in the Dates table

Questions arising from this chapter

Further learning

Quiz about Dates and Date tables

Working with dates

Getting the data model (tables and relationships) right is critical to ensure that your reports perform well, and are maintainable.

Review of CALCULATE - the single most powerful DAX function?

Review of our Income Statement so far

Core skill - Mapping Accounts to a Range

A simple 3-table model and Income Statement

The Gross Profit problem and how to solve it

Quiz about the Income Statement layout and data model

Review of Quiz

Power BI files

A data model for financial reporting

In this chapter, we create an income statement that includes irregular subtotals such as Gross Profit

Income Statement rows and their challenges

Core skill - how to sort Account Names or Statement Rows with duplicate values

A 'Chart of Accounts' table to define statement rows

A Chart of Accounts Bridging table to map 'ChartOfAccounts' to 'Accounts'

Income Statement "Base" and "Actual" measures

Quiz about BridgeAccounts and ChartOfAccounts

PowerPoint Files

The Income Statement

In this chapter, we look at how to customise statement layouts, with blank rows, display expenses as positive values and include rows to show ratios and percentages

Common formatting and calculation needs by row, column and value

Insert Blank Rows

Expenses as positive

Gross Profit & Margin %

Metadata-driven custom subtotals and ratios

Bonus lesson: Using Generic Account Ranges to configure custom charts of accounts

Bonus lesson: Customising the display of a specific cell in a statement matrix

Quiz about customising rows of the income statement

Chapter 5 PowerPoint files

Chapter 5 Power BI files

Updated Accounting Demo Data

Customising statement layouts

In this chapter we will create a simple balance sheet, to show the opening & closing balances by account, as well as movement in the period

The balance sheet rows and implications for our model

A simple "closing" balance sheet

Opening balances and movements

Column variations on a balance sheet matrix

Discussions around Opening Balances and Trial Balance reporting

Chapter 6 PowerPoint files

Chapter 6 Power BI files

Chapter 6 Quiz

A Simple Balance Sheet

In this chapter, we extend the data model to facilitate average, maximum and minimum balance calculations across any period, as we as handle floating account categories that are assets or liabilities, depending on their balance.

Introduction and Agenda

The average balance problem

Core skill - using Performance Analyzer to compare measures

The floating categories problem

Extending the data model to handle average balances and floating accounts

Creating 'Accounts_history' table - part 1

Core Skill - using a custom function to add a running total "Balance" column to 'Accounts_history'

'AccountCategories_history' table with balance-driven floating assignment

Chapter 7 Quiz

Chapter 7 Excel files

Chapter 7 Power BI files

Chapter 7 PowerPoint files

Extending the data model for advanced Balance Sheets

In this chapter, we add the BridgeCategories and AccountCategories tables into our model so that we can correctly assign floating categories. We add additional measures to the report to use these tables, and add a matrix for the balance sheet with dynamic columns, to take into account slicer selections to show movement or closing ballance.

Introduction and recap from Chapter 7

'BridgeCategories' and 'AccountCategories' tables - why we need them

Define the custom subtotals of our Balance Sheet

Create BridgeCategories table

Create AccountCategories table

Balance Sheet Measures using AccountCategories_history

Core skill - dynamic statement columns plus monthly BS

Review of Chapter 8

Chapter 8 Quiz

Excel Files

Advanced Balance Sheets

In this chapter, we will create a cash flow statement based on the direct method. To do this, we  will identify the bank accounts, cash flow transactions, and map these to a custom cash flow statement layout.

How we will create our Cash Flow statement

Flagging Cash Flow Accounts

How to identify the cash flow journals

Flagging the cash flow journals

Defining the cash flow chart rows

Cash flow measures

Review of Chapter 9

Chapter 9 Quiz

Chapter 9 Excel Files

Chapter 9 Power BI Files

Chapter 9 PowerPoint Files

The Cash Flow Statement

In this chapter we add budgets, based on setting up a folder of budget workbooks, with one workbook per year. The same principle can be used for forecasts and other scenarios.

We show how to create an Excel workbook of budgets based on the previous year actuals, adjusted by some factor.

Budgets Introduction

Create Excel table of PY Actuals

Create Excel budgets based on PY actuals 

Read and transform local Budgets folder

Budget Measures

Reading from OneDrive folder

Discussion of other options and scenarios for Budgets and Forecasts

Review of Chapter 10 and other questions

Chapter 10 PowerPoint Files

Chapter 10 Excel Files

Chapter 10 Power BI Files

Chapter 10 Quiz

Budgets & Forecasts

In this chapter, we examine how to create a single report page showing the YTD actuals and the remainder of the year as budget, forecast, or previous year.

Objectives

YTD Measures - part 1 - MaxDate

YTD Measures - part 2 - MinDate

YTD Measures - part 3 - PY Dates Measures

Dates measures for FY "Remainder"

[PL_YTD_Plus] Measure

Fix for Cumulative error in [PL_YTD_Plus]

Formatting the PL YTD Plus with [FORMAT_PL_YTD_Plus]

Review of Chapter 11

Chapter 11 PowerPoint Files

Chapter 11 Power BI Files

Chapter 11 Excel Files

Chapter 11 Quiz

Actuals plus remainder-as-budget Income Statement (YTD+)

In this chapter, we look at applying Row Level Security to the model, adding drill-though pages, and introducing dynamic grouping of Chart of Account rows to an "Other" category, to compress the size of the matrix

Row Level Security

Drill throughs

Group rows to "other"

Chapter 12 PowerPoint Files

Chapter 12 Power BI Files

Chapter 12 Quiz

RLS, Drill through, and Group to Other

The Power BI Framework for Financial Statements comprises a fully working 3-way template as shown below in the form of a .pbix file and sample data. This is combined with over 30 hours’ on-demand video training that takes you through the construction of the template, plus 1.5 hours one-to-one coaching.Try the 3-way template here:<div class="iframe-wrapper"><iframe src="https://app.powerbi.com/view?r=eyJrIjoiYWU3MjM4NmMtZmExZi00NmIwLWEwOTQtZTcyZGExYzVlY2Q1IiwidCI6ImUzYjI5ODJiLTA1MjUtNDc5ZS1hNmYyLWMzNzdjMWQ5ZmZiYyIsImMiOjl9" frameborder="0" allowfullscreen="true"></iframe></div><ul><li>Training is delivered over 12 chapters and 76 videos, giving over 30 hours of practical on-demand video tuition.</li><li>Realistic sample data of a B2B services company is included, with &gt; 300k transactions, together with “snapshot” .pbix files as the course progresses.</li><li>Run as a community, with the goal to share knowledge and experience, all participants will have access to a group chat where they can communicate with each other and also the instructor.</li><li>Up to 1.5 hours one-to-one coaching with the instructor is included (to be used within 12 months of purchase).</li><li>A certificate showing 30-hours tuition for CPD purposes is provided to all students that complete the course.</li></ul>

From fundamental foundations to final finishes, this instructor-led course will take you on a journey that ends with you understanding how to create integrated three-way financial statements in Power BI.

First Unit	Last Unit	Per Unit
1	1	£1,200
2	∞	£600

A comprehensive Power BI 3-Way Financial Reporting Framework

Hugh Johnson

Course Details

Course content

Includes