Income Statement Semantic Models: Building Enterprise-Grade Income Statement Models with Power BI

This comprehensive guide will teach you how to build an income statement semantic model, also known as the profit and loss (P&L) statement. Author Chris Barber— a business intelligence (BI) consultant, Microsoft MVP, and chartered accountant (ACMA, CGMA)—helps you master everything from designing conceptual models to building semantic models based on these designs. You will learn how to build a re-usable solution based on the trial balance and how to expand upon this to build enterprise-grade solutions. If you want to leverage the Microsoft BI platform to understand profit within your organization, this is the resource you need. What You Will Learn Modeling and the income statement: Learn what modelling the income statement entails, why it is important, and how income statements are constructed Calculating account balances: Learn how to optimally calculate account balances using a Star Schema Producing external income statement semantic models: Learn how to produce external income statement semantic models as they enable income statements to be analyzed from a range of perspectives and can be explored to reveal the underlying accounts and journal entries Producing internal income statement semantic models: Learn how to create multiple income statement layouts and further contextualize financial information by including percentages and non-financial information, and learn about the various security and self-service considerations Who This Book Is For Technical users (solution architects, Microsoft Fabric developers, Power BI developers) who require a comprehensive methodology for income statement semantic models because of the modeling complexities and knowledge needed of the accounting process; and finance (management accountants) who have hit the limits of Excel and have started using Power BI, but are unsure how income statement semantic models are built Table of Contents About the Author About the Technical Reviewer Acknowledgments Preface Part I: Modelling and the Income Statement Chapter 1: What Is an Income Statement Semantic Model? What Is the Income Statement? External Income Statement Requirements Standardization of External Outputs Internal Income Statement Requirements What Is a Semantic Model? An Income Statement Semantic Model As a Navigation Tool Slicing and Dicing Drill Down and Drill Through An Income Statement Semantic Model Measures the Accounting Process Dimensional Modelling Evaluation vs. Execution Precision Summary Chapter 2: How the Income Statement Is Constructed Building the Income Statement to Net Income Transaction Entries Compound Journal Entries Modelling Only Part of the Financial Transaction Accruals vs. Cash Basis Adjusting Entries Provision Adjustments Adjusting Entries Reduce Analytical Flexibility Trial Balance Account Balances The Account Tables The Account Balance Calculation Income Statement to Net Income Line-Items Subtotals Mixed Account Types Two Ways of Calculating with Identical Results Subsets Allocating Net Income to Controlling and Non-controlling Interests Non-controlling Interest Is Provided by the Finance Team or System Maintain Analytical Flexibility by Applying Controlling and Non-controlling Interests at the Journal Level Earnings per Share (EPS) Numerator: Net Income Denominator: Weighted Average Shares Weighted Average Shares Is Provided by the Finance Team or System Relevant to Only a Specific Date Range EPS Presentation Summary Chapter 3: Building a Reusable Solution The Sixteen Challenges in Modelling the Income Statement The Nine Calculation Challenges The Four Presentation Challenges The Three Analytical Challenges The Power BI Semantic Model Building a Semantic Model Data Preparation: Transforming the Trial Balance Data Modelling: Creating Relationships to the Trial Balance Calculations: Sum of Trial Balance Value Querying the Semantic Model Using DAX and MDX How the Semantic Model Supports MDX MDX and Excel The Input-Driven Approach Summary Chapter 4: Why Model the Income Statement? Benefits of Modelling the Income Statement Explain the Income Statement Improve Decision-Making Improve Data Quality Gain a Deeper Understanding of Profitability Uncover Insights to Facilitate the Month-End Close Process Identify Corrective Journals: Adjusting Provision Example Assist in Preparing Board Packs: Commentary Example Why Partially Modelling the Income Statement Is Insufficient Argument 1: Business Intelligence Should Focus on Individual Lines in the Income Statement Rather Than Reproducing Financial Reports in Their Entirety, Which Should Be Managed by the Finance System Providing a Holistic View of Profitability Enabling Users to Focus on Value Added Argument 2: Visual Calculations Should Overlay Semantic Model Calculations to Create the Income Statement Reduced Integration Inability to Stress Test the Model Reduced Functionality Multiple Layouts Extendibility Summary Part II: Calculating Account Balances Chapter 5: Conceptual Account Balance Models Calculating Account Balances Using the Trial Balance Conceptual Model Account Balances Net Credit Less Debit Net Debit Less Credit Journal Entry Conceptual Models Why It Is Important to Work at the Atomic Grain How Dimensions Are Identified Handling Multiple Date Dimensions Working at the Atomic Grain Increases the Number of Rows Increasing Beyond the Atomic Grain Summary Chapter 6: Logical Account Balance Models The Trial Balance Logical Model An Example Journal Entry Logical Model Primary Keys Surrogate Keys Composite Keys Foreign Keys Attributes (AT) Degenerate Dimensions (DD) Measures (ME) Aggregations Creating a Single Aggregation Creating Multiple Aggregations Journal Entry Aggregation Logical Models Summary Chapter 7: The Trial Balance Semantic Model Semantic Model Inputs Power Query Transformations Stage 1: Create Shared Expressions Stage 2: Trial Balance Fact Table Stage 3: Group Ownership Dimension Stage 4: Account Dimension Stage 5: Effective Date Dimension Stage 6: Measure Table Building the Data Model Create Relationships Mark As Date Table Dates Sort Order DAX Step 01: Sum Step 02: Account Type Indicator Step 03: Line-items Calculating Line-Items, Subtotals, and Subsets Summary Chapter 8: A Journal Entry Semantic Model Semantic Model Inputs Power Query Transformations Stage 1: Create Shared Expressions Stage 2: Journal Entry Fact Tables Stage 3: Group Ownership dimension Stage 4: Accounts Dimension Stage 5: Effective Date Dimension Stage 6: Customer Dimension Stage 7: Product Dimension Stage 8: Posting Date Dimension Stage 9: Measure Table Building the Data Model Create Relationships Mark As Date Table Date Sort Order DAX Step 01: Sum Step 02: Account Type Indicator Step 03: Lineitems Summary Part III: Producing External Income Statement Semantic Models Chapter 9: The Four Subtotal and Subset Types The Four Subtotal and Subset Options Why Four Options of Subtotal and Subset Are Required Examples of the Four Subtotal and Subset Options Subtotal Option 1: Net Credit Less Debit Subtotal Option 2: All Credit Less Debit Subtotal Option 3: Net Debit Less Credit Subtotal Option 4: All Debit Less Credit Summary Chapter 10: External Reporting Logical Models The External Layout Table External Layout Table Overview Line Name, Income Statement Key, and Is Hidden Income Statement Line Calculations Calculation Type Overview Income Statement Line Formatting Completing the External Layout Table Step 1: Understand the External Income Statement Step 2: Add the Income Statement Keys Step 3: Add the Line Name Step 4: Complete Line-Items Step 5: Complete Subtotals Step 6: Complete Subsets Step 7: Complete Controlling and Non-controlling Interests Step 8: Complete Weighted Average Shares Step 9: Complete Earnings per Share Step 10: Complete Blanks Extending the Trial Balance Logical Model and a Journal Entry Logical Model Extension 1: Adding the Shares Outstanding Fact Table StarSchema.co.uk Example Shares Outstanding Table Extension 2: Creating the Relationship Between Effective Date and Shares Outstanding Extension 3: Adding the Layout Snowflake Dimension Extension 4: Adding a Foreign Key to the Account Dimension Extension 5: Creating the Relationship Between Layout and Accounts External Reporting Logical Model Trial Balance External Reporting Logical Model A Journal Entry External Reporting Logical Model Summary Chapter 11: External Reporting Semantic Models Semantic Model Inputs Power Query Transformations Stage 1: Create Shared Expressions Stage 2: Shares Outstanding Fact Table Stage 3: Layout Dimension Table Creating the Layout Table Add Conditional Logic to Create Subtotal Columns Add Conditional Logic to Create Subset Columns Add Conditional Logic to Create Percentage Columns Add Conditional Logic to Create Show Value Columns Remove Unnecessary Columns and Change Data Types Stage 4: Account Dimension Table Layout 1 Shared Expression Adding the Layout 1 Income Statement Key to the Account Table Building the Data Model Creating Relationships Sort Hierarchy DAX Actuals Calculation Step 04: Subtotals Subtotal Net Credit Less Debit Subtotal Net Debit Less Credit Subtotal All Credit Less Debit Subtotal All Debit Less Credit Actuals Subtotal Step 05: Subset Subset Net Credit Less Debit Subset Net Debit Less Credit Subset All Credit Less Debit Subset All Debit Less Credit Actuals Subset Step 06: Controlling and Non-controlling Interests Step 07: Weighted Number of Shares Step 08: Bespoke Calculations Step 09: Divide Actuals Divide Numerator Actuals Divide Denominator Actuals Divide Step 10: Add Blank Rows Step 11: Show Values Step 12: Combined Calculation Dynamic Format Strings Summary Part IV: Producing Internal Income Statement Semantic Models Chapter 12: Internal Reporting Logical Models The Internal Layout Table Internal Layout Table Overview Completing the Internal Layout Table Step 1: Understand the Internal Income Statement Step 2: Add the Income Statement Keys Step 3: Add the Line Name Steps 4–10: Line-Items to Blanks Step 11: Complete Percentages Step 12: Bespoke Calculations Extending the External Reporting Logical Models Extension 1: Adding the Other Non-general Ledger Information Snapshot Fact Table StarSchema.co.uk Example Other Non-general Ledger Information Table Extension 2: Creating the Relationship Between Effective Date and Other Non-general Ledger Information Extension 3: Adding an Additional Foreign Key to the Account Dimension Extension 4: Creating the Inactive Relationship Between Layout and Account Extension 5: Adding the Layout Version Attribute Internal Reporting Logical Model Trial Balance Internal Reporting Logical Model A Journal Entry Internal Reporting Logical Model Summary Chapter 13: Internal Reporting Semantic Models Semantic Model Inputs Power Query Transformations Stage 1: Create Shared Expressions Stage 2: Other Non-general Ledger Information Stage 3: Layout Dimension Stage 4: Accounts Dimension Table Building the Data Model Creating Relationships DAX Actuals Calculation Step 08: Bespoke Calculations Step 12: Actuals Summary Chapter 14: Security and Self-Service Considerations Security Considerations Self-Service Considerations Consideration 1: What Is Relevant to End Users Removing Information Hiding Information Consolidating Information Consideration 2: How the Semantic Model Should Be Organized Folders Hierarchies Consideration 3: How the Semantic Model Can Be Enriched Descriptions Detailed Row Expressions StarSchema.co.uk Detailed Row Expression Example Summary Chapter 15: Review of the 16 Challenges The Nine Calculation Challenges Challenges 1–6 Challenges 7–9 The Four Presentation Challenges Challenges 10–12 Challenge 13: Enabling Multiple Layouts The Three Analytical Challenges Challenges 14 and 15 Challenge 16: Drilling from Income Statement Lines to Journal Entries Summary Index