Financial Reporting with Power BI: Tips and Tricks

1. Automate your data cleaning and logic steps in Power Query

If you’re still spending hours each month copying, pasting, and manually transforming data in Excel, Power Query is about to change your life! Think of it as a macro recorder that’s actually readable and maintainable—no VBA knowledge required!

What You’ll Learn

In this tutorial, we tackle a real-world scenario that requires lookup logic: converting multi-currency actuals data into a single reporting currency. This involves:

• Data cleanup automation: Removing header rows from our Actuals data
• Table restructuring: Transforming the exchange rate table from a “wide” format (great for reading, terrible for analysis) into a “long” format that Power BI can actually work with
• Advanced lookups: Perform single and multi-column merges that would require concatenation and multiple lookup formulas in Excel—but take just seconds in Power Query
• Custom calculations: Write simple M formulas to handle currency conversion logic, including conditional logic for transactions already in reporting currency

Why this matters

Every step you configure in Power Query is recorded and repeatable. Next month, when you get fresh data, you simply refresh—and all these transformations happen automatically. No more manual work, no more errors from forgetting a step.

Key Takeaway: Power Query works identically in both Excel and Power BI, making it the perfect bridge technology for finance professionals transitioning from spreadsheets to business intelligence tools.

2. Make use of helper tables to define layouts and dynamic calculations

Here’s where Power BI frustrates most Excel users: you can’t just insert a row, type a formula, and calculate a subtotal. But there’s a better way that’s actually more powerful and maintainable than the Excel approach.

The Helper Table Approach

The secret is using a “helper table” (also called a layout table) that defines exactly how you want your Income Statement to appear. This isn’t a workaround—it’s best practice design in Power BI that gives you complete control over:

• Row order: Define precisely which rows appear and in what sequence
• Calculation logic: Specify whether each row comes directly from your Chart of Accounts, is a subtotal, or requires a custom calculation
• Formatting rules: Control whether values display as currency or percentages on a row-by-row basis

The Power of Dynamic Measures

Once your helper table is in place, you’ll build a “chain” of DAX measures that work together:

1. Direct lookups for rows that map to Chart of Accounts headings (Revenue, Operating Expenses)
2. Running totals for subtotals like Gross Profit and Operating Profit
3. Custom calculations for metrics like Gross Margin and Net Margin
4. A dynamic master measure that intelligently picks the right calculation based on row type

Dynamic Formatting: The Final Touch

Here’s where it gets really powerful: using dynamic format strings, a single measure can display as:

• USD when multiple entities are selected
• Local currency (GBP, EUR, etc.) when a single entity is in focus
• Percentage for margin calculations

All this happens automatically based on user selections and the metadata in your helper table.

Key Takeaway: This approach requires more upfront setup than an Excel Income Statement, but it’s infinitely more scalable. Add a new subtotal row? Just add it to your helper table. The measures handle the rest.

3. Add context to tell a story with your finance data

Numbers without context are just numbers. Is £2.5M in revenue good or bad? Your executives shouldn’t have to remember last year’s figures or dig through other reports to evaluate performance. Bring the context directly into your visualizations.

KPI Cards: More Than Just Numbers

The KPI visual (and the newer Card visual) transforms standalone metrics into performance indicators by showing:

• The current value: Your key metric (Revenue, Gross Margin, Net Margin)
• A trend line: Visual indication of movement over time
• Comparative context: Prior year values as a benchmark
• Variance indicators: Automatic calculation of performance vs. target

Time Intelligence Made Simple

Creating comparative measures in Power BI is remarkably straightforward thanks to time intelligence functions. With a properly configured date table, a single DAX function—SAMEPERIODLASTYEAR—automatically shifts any measure back one year. No complex date logic required.

The tutorial demonstrates how to:

• Mark your date table correctly in Power BI (a prerequisite for time intelligence)
• Create prior year versions of your key metrics with just a few characters of DAX
• Configure KPI visuals to display these comparisons effectively

The Details Matter

We’ll also cover important formatting considerations:

• Renaming labels: Change “Goal” to “Prior Year” for clarity
• Variance calculations: Display percentage point differences (4%) rather than percentage of percentage changes (5.8%)
• Consistent formatting: Ensure prior year values match the format of current values

Bonus: MTD/QTD/YTD Flexibility

The example file includes a Month-to-Date/Quarter-to-Date/Year-to-Date selector built with Calculation Groups. This allows users to toggle between different time aggregations while the KPI visuals automatically adjust—showing the appropriate prior year comparison for whichever period type is selected.

Key Takeaway: Executive dashboards should enable split-second performance evaluation. KPI cards with proper context achieve this by eliminating the cognitive load of remembering historical figures or hunting for comparisons.