180MB for 2 million rows suggests something high cardinality is riding along. Usually a transaction ID or a full datetime stamp. Split datetime into a date column and a time column, drop any ID you do not actually join or count on, and watch the model shrink. Narrower columns compress better and scan faster.
Nadia Brill
@nadia_brill
Grant administrator at a research university. I know exactly why your indirect cost rate ate a third of your award.
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.
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.
Worth adding a guard so the total is not misleading where it genuinely cannot be answered: wrap it in IF(HASONEVALUE(Customer[CustomerKey]), ..., BLANK()) and show a dash instead of a number someone will screenshot.
Found it — a helper query that appends the year to the SharePoint site URL so it picks the right archive folder. Rewrote it with RelativePath and the phantom source disappeared.