How I turned 216,858 journal entries into a financial report anyone can explore

Before we start

What is a financial report?

If you’ve never worked with accounting, think of it as three different questions about the same company.

The challenge is that these three pieces should tell the same story. And that’s exactly what I tried to build.

The challenge

When each company tells a different story.

The data already existed. There were three different companies, each with its own journal, and together they had built up 216,858 entries across 2022 and 2023.

The problem came when you wanted to answer something as simple as:

How is the group really doing?

The balance sheet, P&L and cash flow could be viewed separately, but there was no consolidated view that let you move between them without rebuilding the analysis by hand.

The more versions of the same report there are, the easier it is for two people to reach different conclusions from supposedly identical data. My aim was to prevent exactly that.

The idea that changed the project

I wanted every KPI to be able to stand on its own.

While designing the model, I asked myself a question.

What happens if someone asks me where this number comes from?

I didn’t want to answer by opening Excel.

I wanted to answer with a click.

That’s why the whole report revolves around a very simple idea.

Every figure should be traceable down to the entry that created it.

That principle ended up shaping almost every decision in the project.

Today that path really exists: from the trial balance — €187,730,234.71 of debits matching exactly €187,730,234.71 of credits — I can drill down to a sub-account, from there to its general ledger, and from there to a specific entry. Without leaving the report.

How I built it

I started with the model, not the charts.

Many dashboards start by designing visuals.

I did exactly the opposite.

First I built a model able to support three different financial statements without duplicating logic or breaking calculations.

At the centre are FactDiario, with the actual journal entries, and FactPresupuesto, with the budget data. Both connect to the same nine dimensions: Calendar, Companies, Sources, Accounts, Partners, and the four that give shape to each financial statement — dimPyG, dimBalance, dimCF and DimApuntes.

The model’s only bidirectional relationship connects FactDiario with DimApuntes. I left it that way because it was needed to calculate cash flow correctly when filtering by entry type. Everything else keeps a single filter direction, so the model behaves more predictably.

A decision that saved me a lot of trouble

Keeping actual and budget separate.

The small detail that makes the report more reliable

The validation checklist.

There’s one page that usually goes unnoticed.

And yet it’s one of my favourites.

Before analysing results, the model automatically checks whether there are:

• unclassified accounts,
• unbalanced entries,
• mapping problems.

The Unclassified accounts table is recalculated on every refresh. That means the report itself tries to catch errors before they show up as if they were real results.

It seemed more useful to build that safeguard into the model than to rely on manual reviews afterwards.

With the three years of data loaded, the checklist now comes out clean: zero unbalanced entries, zero unclassified accounts. It’s the first screen I look at before trusting any figure in the rest of the report.

The hardest part

Projecting a year end without inventing the data.

The measure that took me longest was probably the year-end projection.

Up to October I use actual data.

From there on, I use the budget.

But doing it without breaking charts or tables was far more complicated. There’s a configurable cut-off date in the model — set to 31 October 2023 — after which every measure automatically switches from actual data to budget, and the chart series stays visually continuous instead of leaving a gap between actual and projected.

The result, month by month: €309,284.99 of actuals accumulated up to the cut-off, against the €173,511.68 the original budget expected for the whole year. The combined year-end projection comes to €196,404.62 — neither the actual nor the budgeted figure, but the best estimate with the information available at each point.

That was probably when I learnt the most about DAX during this project.

What the dashboard tells you

Thirteen content pages. Seven that only appear when you need them.

The report has 20 pages in total. You navigate from a fixed header with five entry points — Home · Checklist · Trial balance · Balance sheet · P&L · Cash flow — but each block hides its own path into the detail.

Each block answers a different question.

• Home → a panel with 12 KPIs that sums up the situation in seconds.
• Checklist → can I trust the data before analysing it?
• Trial balance → its own three-page block: from the aggregated account to the individual entry (Trial balance → General ledger → Entry detail).
• Balance sheet → financial structure and how it evolves, with a second page for the month-by-month detail.
• P&L → four views: quarterly trend, year-on-year comparison, variance against budget and year-end projection.
• Cash flow → where the group’s cash comes from and where it goes.

The other 7 pages don’t appear in any menu: they’re tooltip pages, which Power BI shows floating when I hover over a point on the balance sheet, P&L or treasury trend charts. Instead of the basic tooltip, a mini-page appears with the figure properly formatted and the same visual identity as the rest of the report. Most reports use one or two pages like this; I used seven, one for each chart that needed it.

These are some of the 12 KPIs on the home panel:

Tools

Tech stack

What I learnt

The best dashboard starts long before the dashboard.

If I had to sum up what I learnt from this project, I wouldn’t talk about the 174 DAX measures.

I’d talk about the model.

I learnt that a good dashboard doesn’t depend only on visual design.

It depends on every figure making sense, being checkable and staying consistent when you change company, period or financial statement.

That change of mindset is probably the most valuable thing I take away from this project.