Every organisation hits the same wall with Power BI eventually.

Your actuals live in the data—sales, costs, headcount, whatever. But the numbers that matter most during planning season—budgets, targets, forecasts, don’t come from a database. They come from people. And Power BI, for all its power, is fundamentally read-only.

So what happens? Budgets end up scattered across a hundred spreadsheets, emailed around, versioned into oblivion, and finally re-imported by some poor analyst at 11pm. Every. Single. Cycle.

This week I built the fix: a Microsoft Fabric Data App where users type their budgets directly, and a Power BI report that shows Actual vs Budget — live, the moment they hit save. No spreadsheets. No gateway. No refresh button.

Here’s the full build. 👇

📺 Watch the full walkthrough

The idea in one sentence

The data gives you the actual. The app captures the human number the data can’t know. The report compares them.

That’s the whole pattern — and once you see it, you’ll find a dozen uses: budgets, sales targets, forecast overrides, approvals, manual corrections. Today it’s budgeting.

The architecture

Everything lives in a single Fabric workspace, entirely in the cloud:

  • Fabric Data App (Rayfin) — a small web app where a planner enters a budget at month × category × store grain.

  • Fabric SQL database — the app writes each budget row here.

  • Lakehouse — the actuals land here from CSV.

  • Power BI composite model — Imports the actuals, reads the budget table via DirectQuery, and exposes Budget, Variance, and Achievement % measures.

  • The clever bit: the app reads its dropdown values (categories, stores, countries) live from the semantic model with DAX, so they’re never out of sync.

Data Architecture

Because the budget table is DirectQuery, a new budget appears in the report without a dataset refresh. That’s the magic moment in the video.

How I built it

1. The writeback app — with Claude + VS Code

I scaffolded the app with Rayfin, Microsoft’s framework for Fabric Data Apps:

npm create @microsoft/rayfin@latest -- "FabricPlanningBudgetEntry" --template dataapp --workspace "Retail-Planning-Fabric-App"

Then I defined a single data model — a Budget entity storing month, category, store, and amount. The one design decision that pays off later: store both the key and the name (categoryKey and category), so the budget joins cleanly to the model.

A form, a couple of dropdowns fed live from the semantic model, some validation, and a “recent entries” table. Deploy is one command — rayfin up — which builds the app and provisions the SQL table in a single step.

2. Actuals into a Lakehouse

The actuals were CSVs, so I dropped them into a Fabric Lakehouse and loaded them as tables. All cloud — no gateway required.

3. The composite model

In Power BI Desktop I pointed the actuals at the Lakehouse SQL endpoint and added the Budgets table as DirectQuery from the Fabric SQL database. Keys made the relationships trivial; a generated date table kept things clean; four simple measures finished it.

4. Publish — no gateway

The part everyone gets wrong: for a cloud source, you don’t need a gateway. I set both sources as cloud connections with OAuth and published. Done.

Then the payoff: type a budget in the app → refresh the visual → Actual vs Budget updates instantly.

The gotchas that cost me hours (so they don’t cost you)

This is the part I wish someone had handed me up front:

  • The dataapp template ships without rayfin/tsconfig.json. Without it, deployment silently compiles zero entities and you get an “HTTP 404 / no entities” error. Copy it from the CLI’s own template and add a references entry to the root tsconfig.

  • Turn OFF Auto date/time. I genuinely hate this feature. It creates hidden date tables you never see, bloats the model, and will break your refresh the moment you add a security role.

  • Clean your CSVs before loading to a Lakehouse. Strip the UTF-8 BOM and remove spaces from column headers, or “Load to Tables” rejects them with a cryptic error.

  • Don’t fight the personal gateway. A composite model with a cloud DirectQuery source can’t use one — keep everything cloud and it just works.

  • Lakehouse vs Dataflow Gen2: Lakehouse is simpler and faster for static files. If your source data changes regularly, use a Dataflow Gen2 so it’s a repeatable pipeline instead of you re-uploading.

  • Open the app inside the Fabric portal — the embedded auth only works there, not from the raw hosting URL.

What’s next

The same pattern works for any “observed vs human” comparison. I’m planning an “API → Fabric → writeback” episode — pull live data from a public API (creator analytics vs goals, weather-driven demand planning, GitHub OKRs…) and write the human number back.

Which one should I build? Reply and tell me.

🔗 Resources: https://analyticalguy.tech/files

If this was useful, subscribe — I build Microsoft Fabric and Power BI solutions in public, gotchas and all. And forward it to the one colleague who’s still emailing budget spreadsheets. 🙂

Yours truly,
Analytical Guy
https://analyticalguy.tech