Why expert judgement still is still required to get your Power BI reporting right. 

Lately I am hearing a lot of comments like this: “AI can write code, build reports, and analyse data; do we still need consultants?” 

It's a fair question. Here’s an example of what we see in reality. 
 
This is the story of a Work in Progress (WIP) DAX measure that a frontier AI model wrote (Claude’s Fable 5). The problem was it was wrong by about $400K.  

Which is fine, we make mistakes too. It often takes us a couple of attempts and checks before we get it right. The problem is, is that AI is often confidently wrong, and $400K was a plausible number. Nothing on the dashboard looked broken. It only got picked up because I knew the numbers and the business well enough, and had enough professional scepticism, to not take the AI on face value. 

The engagement: an accounting firm that already had Power BI. 

This was a mid-sized accounting practice who already had Power BI.  
Their practice runs on FYI Elite, and time entries, billings and WIP all flow out of it into their reporting. 

This is an extremely common position for Australian accounting firms: the practice management software (FYI, APS, Xero Practice Manager, and others) gives you operational reporting, Power BI gives you flexibility.  

In this case, the client wanted a new report that answered the question: “What was our WIP balance as at any given date?” 

They has a WIP report already, but it only reported “WIP Right Now” with a simple filter over the fact table: 

WIP (Current) = 
CALCULATE ( 
          [Billable Amount], 
          fyiTimeEntries[Status] <> "invoiced" 
) 

Simple, and it matched what FYI itself reports today. But as it was EOFY, they wanted a point-in-time WIP measure where they could pick any date on a slicer and see the open WIP balance as it stood at on that day. 

This is where understanding the data comes in. A point-in-time measure has to reason about state over time: an entry sits in WIP from its entry date until its invoiced date. That means we need to manipulate filter context and dealing with two sets of dates rather than just one, which is where most amateur report builders will slow down. 

I can tell you from my own experience, that even most accountants wouldn’t be able to explain how to calculate their own WIP if their software didn’t automatically do it for them. 

Enter the AI: 

This is how we work now. We use AI in our workflow every day.  
For this measure we worked with Claude’s Fable 5, describing the fact table — fyiTimeEntries, with status, entry dates, invoiced dates and billable amounts — and the business rule we wanted encoded. 

The important detail: we told it which functions we wanted and why. We specified REMOVEFILTERS over the date dimension so the slicer sets the “as at” point rather than restricting the entries, how statuses should interact, and how the measure needed to reconcile back to the firm's current-WIP number.  

I uploaded a picture of the existing Star Schema semantic model. I also anonymised 10 rows of data and uploaded it that so it could see column names, samples, and data types. 
Most importantly, I described what functions it should use and why. “Use Remove Filters over the date dimension so that the slicer sets the ‘as at’ point rather than restricting the entries”. I didn’t just let it run blind. 

Here's the measure it produced: 

WIP as at Date = 
VAR AsAt = MAX ( 'EntryDate'[Entry Date] ) 
RETURN 
CALCULATE ( 
     [Billable Amount], 
     REMOVEFILTERS ( 'EntryDate' ), 
     fyiTimeEntries[EntryDate] <= AsAt, 
     FILTER ( 
             ALL ( fyiTimeEntries[Status], fyiTimeEntries[InvoicedDate] ), 
             fyiTimeEntries[Status] <> "invoiced" 
                    || ISBLANK ( fyiTimeEntries[InvoicedDate] )   -- ← the problem 
                    || fyiTimeEntries[InvoicedDate] > AsAt 
     ) 
) 

Forget about the DAX and read the above as a sentence and the logic sounds completely reasonable:  
 
“include every entry recorded on or before the “as at” date, unless it's been invoiced — and if it's marked invoiced but we don't know when, keep it in WIP to be safe.” 

Clean, well-structured, fast. Not even a couple of years ago and the scoping and DAX writing to get to here would’ve taken a couple of hours. Now the first draft takes seconds. 

The problem was that it was wrong. 

Roughly $400,000 out of wack. 

Side by side, the new point-in-time measure ran roughly $400,000 higher than the firm's current WIP. 

I knew this was wrong instantly, but for a user of the reports $400K was not really alarming number. WIP genuinely swings by that much month to month, especially around EOFY, when balances are being billed out and/or cleared out.  
Power BI doesn't put a red squiggly line under a wrong number; as the formula syntax is right, a right number and a wrong number look identical. 

Put yourself in the shoes of who this report was being built for. A manager at the accounting firm opens the report. They see their correct client's name, a column labelled WIP, today's date, and a figure in a plausible range. Every cue on the screen says, “this is your number.” Why would they question it? In our experience, they don't. Numbers get trusted because they look like the truth: right client, right label, right ballpark. 

The only reason it didn't with us is our experience: the two measures were pointed at the same “as at” date, so they had to match. They didn't. So, we went client by client, and 90% of them matched. But with a troubleshooting variance column, and found rows like these: 

(Client names changed; figures are real.) 

Dozens of clients, each carrying its own slice of the $400K variance. 

Diagnosing the Problem: 

We eliminated the obvious suspects first; visual filters, page filters, entity groupings. This is 101 for Power BI Analysts. 
We even baked the status filter directly into a measure so no report-level filter could possibly interfere. The variances held. So, we knew now it wasn’t the filter context. 

So we recreated the data set for Harrington group's alone, the $4,500 culprit was a single manually keyed, invoice-type entry: status “invoiced”, $4,500, with no entry date and no invoiced date at all. 

Why did that one row split the two measures?

WIP (Current): status = “invoiced” → excluded. ✓ 
WIP as at Date: two things went wrong at once. 

In DAX, a blank date silently coerces to 0 — which is 30 December 1899. So fyiTimeEntries[EntryDate] <= AsAt evaluated as true for a row with no date at all. The row then fell straight into the ISBLANK ( InvoicedDate ) branch — the “keep it in WIP to be safe” logic — and landed in the balance. ✘ 

Multiply that pattern across every manual adjustment and dateless invoiced entry in the book, and you get your $400,000. 

When the AI first simulated the data to “verify” the measure, it modelled blank dates the way most programming languages do: blank fails the comparison. So, it confidently reported that everything reconciled. DAX does the opposite. The AI's verification was wrong, and the dashboard displayed the incorrect balance with precisely the same confidence as the correct one. 

The fix: 

Once you can see the failing row, the fix is two lines of logic: an invoiced entry may sit in WIP only up to a known invoice date and an invoiced entry with no dates at all should never appear in point-in-time WIP, matching exactly how the firm's current measure treats those entries today. 

WIP as at Date = 
VAR AsAt = MAX ( 'EntryDate'[Entry Date] ) 
RETURN 
CALCULATE (
     [Billable Amount],
     REMOVEFILTERS ( 'EntryDate' ), 
     fyiTimeEntries[EntryDate] <= AsAt,
     FILTER ( 
              ALL ( fyiTimeEntries[Status], fyiTimeEntries[InvoicedDate] ), 
              fyiTimeEntries[Status] <> "invoiced"
                    -- ← the fix:
                    || ( NOT ISBLANK ( fyiTimeEntries[InvoicedDate] )
                          && fyiTimeEntries[InvoicedDate] > AsAt )
     )
) 

The change looks minor. ISBLANK becomes NOT ISBLANK inside an ‘AND’. This flips the treatment of an entire class of rows. Reconciled against exported source data: Harrington to $14,064.29, Broadwater to $8,541.19, every client to the cent, firm-wide variance to zero. 
 
We explained it to the AI and it fixed it at speed. Great. 

The fix only happened however because of three things: 

1. Business knowledge: knowing that a dateless, manual invoice adjustment is a possible outcome in the FYI Elite data. I knew this was because of the way a data migration was handled when they took up this software a couple of years earlier. 
2. Reconciliation discipline: Accountants know this as professional scepticism, refusing to accept the numbers on face value and ensuring the new measure ties back to a known source number, per client, to the cent, before any end user ever saw it. 
3. Deep DAX knowledge: knowing that blank-date coercion to 1899 is even a possible failure mode and recognising its fingerprint in the variances. 

We're not the only ones seeing this 

This experience mirrors what respected voices across the Power BI community are reporting: AI solutions that look right and even ‘work’ but carry fragility underneath. 

The emerging consensus is one we'd endorse from direct experience: 

In expert hands, AI is a genuine multiplier. We ship better solutions, faster, because we can direct the tool precisely and critically validate what comes back. 

In inexperienced hands, AI lets people build things they never could before. Including problems they never could have built before. The errors don't announce themselves. They compound quietly and in the end users make business decisions off incorrect assumptions. 

Now scale that up 

This was one measure. And realistically, a relatively simple formula. 
But it was one filter branch, one blank-date edge case and a $400,000 swing that looked entirely believable. 

Microsoft is rapidly expanding AI's reach including the ability for AI agents to build entire semantic models and reports through Power BI's MCP integration. That's genuinely exciting for us as developers, and we're actively working with these capabilities. 

But think of our example, then multiply it: every relationship direction, every filter choice, every date-table decision, every status field with a quirky legacy value, every measure interacting with every other measure, across a full dataset and report suite. 

Who reconciles it? Who knows what the numbers should be? Who bothers to check every single value and output back to the source data. 

What this means for your firm 

If you're an accounting practice (or any business) running Power BI alongside practice software like FYI Elite, we aren’t saying to avoid using AI. 
The example I gave to my accounting client was “Imagine if I just had the AI do complex structuring advice and tax planning”. That’s usually enough for them to understand from their point of view that things are more nuanced and requiring a holistic approach than AI can handle. 

The takeaway is that the value has shifted from writing syntax to: 

- Defining the business rules a measure must include and knowing when a rule is ambiguous 
- Knowing your source system's data quirks. Every system is different, and every client uses their system slightly differently. 
- Reconciling every number back to a source of truth before anyone trusts it 
- Recognising the failure modes that don't throw errors 

That's the work Vision BI does every day. 

AI writes the first draft in seconds now. We make sure it’s right. And on the evidence of one blank date and $400,000, it's going to stay that way for a while yet. 

Vision Business Intelligence is a Queensland-based Power BI and data analytics consultancy working with accounting firms and businesses across Australia. If your reports don't reconcile — or you're not sure anyone's checked — get in touch.