Measure is Sales LY = CALCULATE([Sales], DATEADD('Date'[Date], -12, MONTH)). For most months it lines up with last year fine, but four or five months come back completely blank even though there are definitely sales rows in those months a year earlier. The date table is a query off my fact table's order dates, and I marked it as a date table. Same behaviour in a card visual, so it is not the matrix.
Also confirm the measure is filtering on the date table's column and not the fact table's date column. Half the time people mark a proper date table, then write the time intelligence against Orders[OrderDate] out of habit and get results that are almost right, which is the worst kind of wrong.
Yes, and hide the fact date column once the relationship exists. If it is not in the field list nobody picks it by accident.
Longer term I would move off DATEADD for month comparisons and use an integer month offset column instead. Add MonthIndex = (Year * 12) + Month to the date table, then last year is just this month's index minus 12. It survives gaps, partial months and 4-4-5 fiscal calendars, none of which the built-in functions handle gracefully. Slightly more setup, far fewer mystery blanks later.
Sounds like a relationship direction thing to me, try setting the cross filter to Both and see if the blanks fill in.
I would not do that here — bidirectional filtering on a date relationship causes ambiguity as soon as a second fact table appears, and it will not conjure up missing calendar rows anyway. The gaps explanation above matches the symptom exactly.
@idempotent_ian · 3mo ago · 3 replies
Your date table is built from distinct order dates, so it has holes in it — every day you took no orders is simply missing. Time intelligence functions require a contiguous calendar, and when DATEADD shifts into a range where some days do not exist as rows it quietly returns blank instead of erroring.
Rebuild it as its own table:
Date = CALENDAR(DATE(2021,1,1), DATE(2027,12,31)), add your year and month columns off that, relate it to the fact table, and re-mark it as the date table. Then delete the old one so nothing sneaks back in through an old relationship.Reply
Report
@csv_apologist · 3mo ago
That was it. Eleven missing days across the year, all Sundays and two public holidays. Rebuilt with CALENDAR and every month populates now. I had no idea it failed silently rather than complaining.
Reply
Report
@pinned_versions · 3mo ago
Worth adding: the calendar should cover whole years, 1 Jan to 31 Dec, not just min to max of your data. Year-to-date measures get strange at the edges otherwise.
Reply
Report