Your measures come back empty. Blame the date table

You build a calculation that looks at the last date in your model. Revenue for the last month, the position as of today, the comparison with the same period last year.

And you get nothing back. No error, no zero. Just empty.

Then you start doubting your formula, while it is probably fine.

What causes it

Every decent data model has a separate date table in it: a table with all the days, which you link your facts to. That is good practice and you need it to calculate reliably across periods.

That date table is almost always made wider than the data. From a few years back to a few years ahead, so you never have to adjust it again. Completely reasonable.

But then you write a calculation that looks for the last date in the date table. It finds it neatly, and that is a date far in the future. There is not a single invoice on that date. So your calculation looks at a period with nothing in it and returns empty.

I have had this myself in a model where the date table ran years further than the invoices. Everything was correct, only every calculation anchored on the last date came back empty.

The fix

Do not anchor on the last date of your date table, but on the last date for which you have data.

In practice that means: first determine the maximum of the date in your fact table, not in your date table. That single distinction solves most of this category of errors.

A second, even simpler approach: put a flag in your date table that says whether a day lies in the past relative to your data. Then you can filter out everything in the future in one go, in every report, without having to adjust each calculation separately.

Which of the two you pick depends on your model. The point is that you choose deliberately, instead of assuming that your date table and your data run equally far.

The variant that is worse

There is a quieter version of this problem, and it costs more.

Suppose your date table does stop at today, but your data runs to halfway through this month because the rest has not been processed yet. Then your last month is incomplete, and your average per month gets dragged down by a month that is not finished.

That does not give an empty result. It gives a result that looks reasonable and is wrong. Everyone looks at it and nobody notices.

The defense against that is being explicit about completeness. Mark in your model which periods are closed, and let calculations about averages or trends run only on those periods. The running period you show separately, with a label saying it is still running.

How to catch this early

Two checks you set up on every model, and they cost ten minutes.

Put somewhere visible what the earliest and the latest date in your facts is. Not in your date table, in your facts. If that deviates from what you expect, you know straight away.

And count the number of rows per month over the last twelve months. A month that stands out upward or downward is either a real story or a processing problem, and you want to know about both.

The claim

When a calculation comes back empty, you suspect your formula. Look at your date table first. In most models it runs further than reality, and then a correct formula is calculating over a period where nothing happened.