Month-over-Month Where the Months Are Missing
An account balance table holds one row per account per month, but only for months where the account had activity. There are 300 accounts and 24 months, so a dense table would be 7,200 rows; the actual table has 5,140. The analyst needs month-over-month change in balance per account. The first attempt used a Multi-Row Formula grouped by account, referencing the previous row's balance, after a Join to a month lookup. Totals reconcile for about 80% of accounts but are wildly wrong for accounts with gaps - one account compares March against January. A second problem: a re-run of the upstream process produced two rows for the same account and month for 60 account-months.
1. Explain precisely why the Multi-Row Formula gives the wrong prior value for gapped accounts, and state the two preconditions the tool needs before it can be trusted.
Read the worked answer
What the tool actually does. [Row-1:Balance] means the record physically before this one in the data stream. It has no concept of a calendar. For an account with March and January but no February, the March row's predecessor is January - so the formula dutifully computes March minus January and presents it as month-over-month change. It is not a bug; it is the tool doing exactly what it was told.
Two preconditions.
- Sort order. Months must be ascending within each account. If the stream arrives grouped by month instead of by account, or in arbitrary order,
Row-1is meaningless. Sort explicitly (Sort tool: account ascending, month ascending) rather than trusting whatever order the previous tool emitted. - Contiguous grouping. The group-by setting in the tool compares against the immediately preceding row within the current group. If an account's rows are scattered across the file, the tool cannot see the earlier rows as belonging to that group. Sorting by account first is what makes each group contiguous.
And then a third issue that neither precondition fixes: even with perfect sorting and grouping, a calendar gap still means the previous row is an older month. The tool cannot invent the missing February row. Multi-Row Formula is only correct on a dense series; making the series dense is a separate job, done upstream. If a gap cannot be filled, the honest output is a null change with a flag, not a number that looks like a monthly delta.
2. How do you make the table dense, and what decision has to be made before you can?
Read the worked answer
Build a scaffold, then left join the facts onto it.
- Unique (or Summarize Group By) the fact table to get the 300 account keys.
- Append Fields those keys to a 24-row month list - a Text Input or Generate Rows. 300 × 24 = 7,200 rows, which is the target density and is trivially small.
- Left join the actual balances onto the scaffold on account + month.
Verify the join. The row count after the join must still be exactly 7,200. If it is higher, the fact side has duplicates (the 60 account-months from the setup) and the scaffold just inherited them. Fix that first - see the next task.
The decision you cannot avoid. After the left join, a null balance in a month means one of two very different things:
- The account existed but had no activity. The balance should probably carry forward from the prior month, or be zero if the account was closed. Only the business knows which.
- No relationship exists. If the scaffold was built from the wrong key list, the null is a data error, not a business fact.
If balances carry forward, you need a second pass - sort by account then month, then a Multi-Row Formula that fills the current balance from [Row-1:Balance] when it is null. Order that pass after the scaffold join and before the change calculation, and re-sort in between, because a Join tool does not promise to preserve order. If zero is correct instead, a simple IIF(IsNull([Balance]), 0, [Balance]) does it - but write the rule down, because the two choices produce different month-over-month numbers and someone will ask in six months why January shows a step change.
3. For the 60 duplicated account-month rows, how do you keep one - and why does the answer depend entirely on sort order?
Read the worked answer
Unique keeps the first record it meets. That is the whole behaviour. It does not evaluate which row is better; it takes whichever arrives first for each key. So the surviving row is decided by the row order of the stream, which means the row order is part of your business logic and must be made explicit.
Make 'first' mean 'correct'. Sort deliberately before the Unique tool - for example account ascending, month ascending, last_updated descending - so the first row per key is the most recently written version. Without that sort you are keeping an arbitrary duplicate and the result can change between runs on identical data, which is the worst kind of bug.
Check the alternatives. Summarize with Group By account+month and Max(last_updated) alongside Max or Sum for the measures is order-independent and reproducible, which is attractive. Two cautions: picking Max per field independently can synthesise a record that never existed as a row (an updated timestamp from one version stitched to a balance from another) - if you need whole rows, use the sort + Unique approach instead. And Summarize does not guarantee output order, so if a Multi-Row Formula runs afterwards you must re-sort.
Then prove it. Group By account+month with a Count and assert the maximum count is 1, or compare the Unique's two outputs (unique vs duplicate counts) and log the duplicate count to a Message tool. If the 60 account-months are symptomatic of an upstream re-run problem, the fix belongs upstream; the Unique tool is a stopper, not a cure, and it will hide the recurrence unless you count what it swallowed.