The report filtered by the wrong date

Every row has more than one date. The report used one of them and didn’t say which.

Sound familiar? Someone swears they logged their hours, and the report says they didn’t. The weekly total changes depending on when you run it. And the fix everyone reaches for is “let’s check again on Monday.”

What happened

On a client engagement, a team logged time against tickets, and a weekly report rolled those hours up. It had a date slicer at the top. Pick a week, see that week’s hours. Simple.

Then people started reporting gaps: work they’d done and logged, that they could see in the source system, just not in the report. The usual suspects got the blame first — a sync that didn’t run, a pipeline that dropped rows, someone logging time in the wrong place.

None of that was it. The slicer filtered on the ticket’s date — when the ticket was opened — not the date the work happened. A ticket opened a month ago with hours logged on it this week didn’t show up in “this week” at all. Those hours were sitting, correctly, four weeks back.

It’s an easy mistake to make. The ticket’s date was the first date in the table, so it became the default. And for most tickets — opened and worked in the same week — the two dates agree. The report was right most of the time, which is exactly what made it so hard to doubt.

Here’s why it took a week to find: every check people ran used the same report. Rerun it, and the hours are still missing. Filter it differently, and they’re still missing. The data was right. The report was answering “what work was done on tickets opened this week?” when everyone was asking “what work was done this week?” The slicer just said “Date.”

What we changed

The fix was small. The weekly report now filters on the work date, and the slicer says so in plain words. The ticket’s date didn’t go away — it’s still useful for questions about how old the work is — it just became its own clearly named filter instead of the default. And we added one reconciliation check: the week’s total in the report has to match the week’s total in the source system. If it doesn’t, that’s the first thing anyone looks at, before a single pipeline gets blamed.

This isn’t a time-tracking problem. Every table worth reporting on carries several dates: created, worked, closed, invoiced, booked. Pipeline reports pick between created date and close date. Revenue reports pick between booked and recognized. A date filter quietly picks one, and the label rarely tells you which.

What to take from it

• Every row has several dates. Decide which one each report answers to, and write it down.
• Label the slicer with the date’s real name — “Work date,” not “Date.”
• Default to the date the thing actually happened. Keep the others as named, secondary filters.
• Reconcile one total against the source before anyone calls data “missing.”
• When records disappear, check the filter before the pipeline. It’s the cheaper suspect, and it’s guilty more often than you’d think.