Live sessions for this chapter are completed. Recordings for the lessons are available until the end of 2025.

Accounting demo data

Introduction and objectives

Accounting first principals for reporting

Excel v Power BI fundamental differences

The Power BI Filter Context

The Power BI Row Context

Summary, questions and further reading

Foundations quiz

Power BI Files

PowerPoint files

Foundations - essential accounting and Power BI concepts

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

Quiz about Dates and Date tables

Fixing an error in the Dates table

Working with dates

Date-related questions from Chapter 2

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

Summary, questions and further learning

Quiz about the Income Statement layout and data model

Review of Quiz

Power BI files

A data model for financial reporting

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

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

Questions arising from Chapter 5 - Customising Statement Layouts

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

Chapter 6 PowerPoint files

Chapter 6 Power BI files

Chapter 6 Quiz

A Simple Balance Sheet

Discussions arising from Chapter 6 around Opening Balances and Trial Balance reporting

Introduction and Agenda

The average balance problem

Core skill - using Performance Analyzer to compare measures

The floating accounts 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

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

Chapter 8 Quiz

Excel Files

Advanced Balance Sheets

Date/time options:
Thu Dec 5th 09:00 - 12:00 UTC-8 (Los Angeles)

Review of Chapter 8

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

Chapter 9 Quiz

Chapter 9 Excel Files

Chapter 9 Power BI Files

Chapter 9 PowerPoint Files

The Cash Flow Statement

Review of Chapter 9

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

Chapter 10 PowerPoint Files

Chapter 10 Excel Files

Chapter 10 Power BI Files

Chapter 10 Quiz

Budgets & Forecasts

Date/time options:
Thu Nov 21st 09:00 - 12:00 UTC (London)

Review of Chapter 10 and other questions

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]

Chapter 11 PowerPoint Files

Chapter 11 Power BI Files

Chapter 11 Excel Files

Chapter 11 Quiz

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

Date/time options:
Thu Nov 28th 09:00 - 12:00 UTC (London), 
Thu Dec 12th 09:00 - 12:00 UTC-8 (Los Angeles)

Review of Chapter 11

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, combined with instructor-led training that takes you through the construction of the template.<div class="iframe-wrapper"><iframe src="https://app.powerbi.com/view?r=eyJrIjoiN2Q0NGRkM2UtMDQzMS00Njg0LTgxNGEtOGRlZTIyZDFiMDE5IiwidCI6IjA0NmZlOGZmLTMzZWMtNDkzZC1iYjg3LWU2MDY2ZDI1ZjZkNiIsImMiOjh9" frameborder="0" allowfullscreen="true"></iframe></div> Training is delivered through an instructor-led course, run over 12 weeks, with 3 hours' online tuition per week, broken down into a total of 56 lessons. The course is run in three weekly-staggered streams to accommodate attendees from different parts of the world and with changing personal diaries.All students can mix and match from the three streams as they please and as suits their diaries.EMEA stream - 09:00 to 12:00 (London) every Thursday for 12 weeks commencing 12th September 2024Americas stream - 09:00 to 12:00 (Los Angeles) every Wednesday for 12 weeks commencing 18th SeptemberAPAC stream - 09:00 to 12:00 (Canberra) every Tuesday for 12 weeks commencing 24th SeptemberVideo recordings of each lesson will be available online to all registered students for a period of 12 months. Realistic sample data of a B2B services company with &gt; 300k transactions, together with “snapshot” .pbix files as the course progresses.As this is an instructor-led course, students are encouraged to ask questions.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.A certificate showing 32-hours tuition for CPD purposes is provided to all students that complete the course.

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.

A Power BI framework for 3-way financial statements

Hugh Johnson

Course Details

Course content

Includes