A relational data model built entirely inside Excel's Power Pivot engine, simulating a digital wallet / neobank's core data: customers, accounts, transactions, loans, and merchants — connected through real one-to-many relationships and analyzed with DAX measures, capped off with a cross-filtering interactive dashboard.
This project was built to demonstrate a step beyond flat-table Excel work: designing an actual multi-table schema, understanding relationship cardinality, and writing DAX rather than worksheet formulas.
| Dataset | Synthetic digital wallet dataset — 250 customers, 336 accounts, 10,500 transactions, 75 loans, 40 merchants |
| Tools | Microsoft Excel — Power Pivot Data Model, DAX measures and calculated columns, PivotTables, slicers |
| Focus | Relational schema design, DAX measure-writing, time-intelligence, interactive dashboarding |
Five tables, each with a clear grain (what one row represents), connected through explicit relationships rather than VLOOKUPs between flat sheets:
Customers (1) ──< (many) Accounts (1) ──< (many) Transactions (many) >── (1) Merchants
│ │
└──< (many) Loans └──< (many) [via Transaction_Date] Dates (1)
Loans ──< (many) [via Date_Issued] ──┘
- Customers — one row per customer: region, KYC tier, signup date
- Accounts — one row per account (a customer can hold more than one — Wallet and/or Savings)
- Transactions — one row per transaction (10,500 rows, the largest fact table);
Merchant_IDis deliberately left blank for non-purchase transaction types - Loans — one row per loan, linked directly to Customers (a loan belongs to a person, not a specific account)
- Merchants — one row per merchant, referenced by Transactions
- Dates — a continuous calendar table (no gaps), required for any month-over-month or year-to-date analysis
Two deliberate data-modeling decisions worth calling out:
-
Nulls are allowed in
Transactions[Merchant_ID]for Top-up/Withdrawal/Transfer transaction types, since those genuinely aren't purchases at any merchant. This is standard practice in real fact-table design — not every fact needs to satisfy every dimension relationship — and it means any merchant-based measure naturally and correctly excludes non-purchase activity without extra filtering logic. -
KYC tier enforces real constraints on the data, not just describing it: Basic-tier customers are never eligible for loans (mirroring actual KYC/AML regulation in digital banking), and transaction amounts respect a per-tier ceiling (Basic ≤₱10,000, Verified ≤₱50,000, Premium ≤₱500,000). This makes tier-based measures meaningful rather than cosmetic — and it means a Basic-tier customer somehow having a loan would be a genuine anomaly worth investigating, not just noise.
Unlike the other tables, the Dates table isn't sourced from business data — it's scaffolding required for DAX's time-intelligence functions (DATEADD, TOTALYTD, etc.) to work at all. These functions need a continuous, gap-free calendar to reliably answer "what was last month" or "what's the year-to-date total" — a transaction log alone has gaps on any day nothing happened, which breaks that logic silently.
A real build issue worth documenting: the first version of this table, generated via Power Pivot's automatic CALENDARAUTO() shortcut, scanned every date column across the entire model to determine its range — which pulled in unrelated dates from other tables and produced months with no real transaction activity, silently skewing growth calculations with false swings. The fix was rebuilding the Dates table with an explicit date range scoped to the actual transaction window, using a DAX query table (EVALUATE CALENDAR(...)) added to the Data Model. This is the kind of modeling issue that's easy to miss until a measure produces a number that doesn't make sense — worth watching for in any Power Pivot project with more than one date-type column.
Eleven measures were built across the model — see the accompanying DAX_Measures_Documentation.txt for a full explanation of each one's logic, syntax, and purpose. In summary, they cover:
- Volume & activity — Total Transaction Volume, Active Accounts (distinct, not raw transaction count)
- Lending risk — Total/Defaulted Loans, Loan Default Rate, Average Loan Principal, Total Outstanding Principal
- Merchant analysis — Total Merchant Spend (isolating purchase-type transactions only)
- Time intelligence — Transaction Volume Prior Month, Month-over-Month Growth %, Year-to-Date Transaction Volume
A single Dashboard sheet with four cross-connected PivotTables — KYC Tier summary, Regional loan/spend breakdown, Merchant Category spend, and the monthly trend table — all controlled by three shared slicers (Month, Region, Tier). Selecting any slicer value updates all four tables simultaneously, since every PivotTable draws from the same underlying Data Model rather than separate ranges.
Every filtered result was spot-checked against the raw source data (e.g., filtering to a single region correctly recalculated merchant spend, loan default rate, and active account counts, matching hand-calculated totals from the source tables exactly).
- Designed a genuine multi-table relational schema in Excel, not just a flat table with lookups
- Applied real-world domain constraints (KYC tier limits) directly into the data generation, making downstream measures meaningful rather than arbitrary
- Learned and debugged a non-obvious Power Pivot pitfall (CALENDARAUTO scanning the whole model) — a real troubleshooting story, not just a tutorial walkthrough
- Built genuine DAX time-intelligence measures (DATEADD, TOTALYTD) that depend on correct relational modeling to work at all
- Delivered an interactive, cross-filtering dashboard rather than a set of static tables
DataModel(FintTech).xlsm— full workbook (five source tables, Dates table, Power Pivot Data Model with relationships, all DAX measures, Dashboard sheet)DAX_Measures_Documentation.txt— full explanation of every measure and calculated column used