Treasury Analytics Dashboard

The Problem

Treasury data doesn't behave like typical financial reporting data.

In most finance dashboards, you're working with transactions: journal entries, invoices, payments. They have a date, an amount, and they aggregate cleanly. Sum them up by month — done.

Treasury is different. You're tracking two fundamentally different types of information at the same time:

• Point-in-time balances — bank account balances, FX rates, outstanding debt positions. These are snapshots: what's the number on this date? Summing them across time is meaningless.
• Period-based flows — interest paid, average borrowing rates, FX trading volumes. These accumulate over a month, a quarter, a year.

Both need to coexist in the same data model, respond to the same date slicers, and produce correct numbers whether a user is looking at a single day or a full quarter.

On top of that: the data is multi-currency, sourced from multiple systems with different update frequencies, and full of gaps — no bank reports on weekends, no FX rates on holidays, no debt data on days when nothing changes.

The previous process was Excel-based. Manual. Slow. And increasingly fragile as the number of entities, currencies, and reporting requirements grew.

The Data Model

I built a Power BI dashboard to replace the manual reporting — but the real work wasn't in the visuals. It was in the data model underneath.

Hover on a table to highlight its relationships. Solid lines are active relationships; dashed lines are inactive (role-playing) — activated per measure depending on the business question.

Three fact tables share the same date dimension, but each uses dates differently. Daily balances have a single active date relationship. FX trades likewise. But debt positions need three inactive date relationships — start date, end date, and monthly closing. This is the core modeling challenge, and everything else flows from it.

Modeling the two types of measures

The core design decision was how to handle point-in-time snapshots and period flows in a single star schema.

For balances (cash, FX rates), the source data only has rows when something changes — no entry on a Saturday means the Friday balance still applies. But Power BI's date table has every day. If you use a standard SUM, weekends show zero. That's wrong.

The solution: carry-forward measures using LASTNONBLANKVALUE. The measure scans backward through the date dimension to find the last day with actual data and returns that value. This means a Monday slicer correctly shows Friday's closing balance, and a monthly view shows the month-end position — not a meaningless sum.

For period measures (interest expense, trading volumes), standard time intelligence works — DATESMTD, DATESYTD, and similar patterns. But these measures operate on a different date column than the balance measures, which creates the next challenge.

Role-playing dates

A single loan record has multiple relevant dates: when it starts, when it matures, when interest is due. Each date answers a different business question:

• "What's our outstanding debt today?" → filter by Start Date ≤ today AND End Date > today
• "How much interest did we pay this month?" → filter by the interest payment date within the month
• "What matures next quarter?" → filter by End Date within the quarter

Power BI only allows one active relationship between two tables. I used inactive relationships with USERELATIONSHIP to activate the right date context per measure. The debt position measures use date-range filtering (Start Date / End Date brackets), while the interest measures activate the End Date relationship for period accumulation.

Multi-currency FX conversion

The dashboard covers multiple currencies. Every balance and debt position needs a CHF equivalent, converted at the correct rate for the correct date.

The challenge: FX rate data has gaps (weekends, holidays), and the rate table isn't aligned 1:1 with the transaction dates. The conversion logic finds the latest available rate for each currency as of the reporting date, then applies it row by row:

1. For each position, identify the currency
2. Look up the most recent FX rate on or before the reporting date
3. Multiply and aggregate

This runs inside a SUMX iterator — not the most elegant DAX pattern, but necessary when the rate lookup depends on both the row's currency and the report's date context.

Covenant monitoring

The most complex modeling challenge was loan covenant tracking. Covenants have their own rhythm — semi-annual test dates — and their thresholds can change over the life of a facility.

The measures need to:

• Find the nearest covenant test date relative to the current view
• Look up the applicable threshold (which version was valid at that point?)
• Compare it against the current net debt position (itself a combination of point-in-time debt and cash balances)

This required KEEPFILTERS to preserve the covenant table's own filter context while independently calculating the debt and cash components.

Key Decisions

Decision	Why
Carry-forward over data duplication	Could have pre-filled missing dates in Power Query. Chose DAX-side carry-forward to keep the fact table lean and the logic transparent.
Inactive relationships over separate date tables	Some models use one date table per role. I kept a single DimDate with inactive relationships — simpler to maintain, and USERELATIONSHIP makes the intent explicit in each measure.
Row-level FX conversion over pre-calculated columns	Pre-calculating CHF amounts in Power Query would be faster at query time but creates a maintenance problem: any rate correction requires a full refresh. Doing it in DAX means the conversion always uses the latest available rate.
Separate measure tables	~100 measures organized into three tables (Cash, Debt, FX Trading) rather than attached to fact tables. Easier to find, document, and maintain.

Results

• Replaced a manual Excel process that required hours of data gathering and formatting
• Consolidated cash positions, debt portfolio, FX exposure, and covenant status into a single interactive report
• Users can slice by date, currency, entity, and bank — combinations that weren't feasible in the manual process
• Carry-forward logic eliminated the "weekend zero" problem that plagued earlier attempts

What I Learned

The data model is the product. The visuals took maybe 20% of the total effort. The other 80% was getting the model right — relationships, measure logic, handling edge cases. A clean model makes everything downstream easier; a messy one makes every new visual a fight.

Treasury and standard BI patterns don't always mix. Most Power BI tutorials assume transactional data. Point-in-time balances, date-range filtering, and multi-currency conversion aren't exotic requirements in corporate finance — but they're underserved in the BI community's standard playbook.

LASTNONBLANKVALUE is the unsung hero of finance dashboards. Any dataset with irregular reporting frequencies — bank balances, market data, manually updated KPIs — benefits from this pattern. It's simple, performant, and it solves a problem that trips up most first attempts at financial dashboards.

---

Built with Power BI Desktop, DAX, and Power Query. Data model includes 30+ tables, 100+ measures, and integrates data from FX trading platforms, treasury management systems, central bank rate feeds, and tax authority data.