User Defined Aggregations with Time Intelligence

Hi JamesMcEwan ,

The reason you're seeing this behavior is that your aggregation table is still in use when you add the daily date column to your visual. Power BI will use an aggregation if it can fully answer the query, and in your model, the month anchor column in the aggregation table remains valid for daily queries. Changing the relationship from the Date column to the Month wouldn't resolve this and would actually result in a many-to-many relationship, which isn't supported for aggregations.

To address this, keep your main Date table as it is for time intelligence and daily reporting. Add a separate Month table with one row per month and a MonthStart column. Relate this Month table to the AggDate column in your aggregation table and set up your aggregation mappings accordingly. Month visuals will use the Import aggregation via the Month table, while daily visuals based on the Date table will default to the DirectQuery fact table. This setup ensures clear, consistent behavior without affecting your time intelligence calculations or creating unsupported relationships.

Thank you.