Time Intelligence and Fiscal Calendars - I bet this one isn't new to you, right? :)

Hi all,

I’m working on a dashboard with a fairly standard requirement, but I’m struggling to find a clean and scalable way to implement it properly.

The idea is to let users select a timeframe (week, month, quarter, year), then choose a specific reference period that drives the actual values, and finally define a comparison type (for example MoM or YoY depending on the selected timeframe) to calculate prior period performance. The output is a classic executive summary dashboard: Revenue, Profit, Margin %, trends and a breakdown by product.

From a modeling perspective, the fact table is at weekly granularity and won’t go lower due to row explosion, and I’ve added a calendar dimension based on the fiscal one.

So far I’ve explored a few approaches, none of which fully convince me.

The first is pushing everything upstream in SQL, following the practices of my team's analysts/scientists: building an expanded fact table, which granularity already includes the actual x comparison period combination, and that comes with pre-aggregated metrics for each KPI_Actual and KPI_Previous column. This makes Power BI very simple, but it feels rigid, hides a lot of complexity in the SQL layer, and limits the ability to drill into the selected periods due to the table structure. It also makes the calendar dimension almost redundant, which doesn’t feel right long term.

The second approach was to rely on indexed columns (like YearMonth or YearQuarter as numeric keys) and shift periods in DAX. This works, but it feels quite fragile and not something I’d want to maintain over time. I am afraid this kind of 'handmade time-intelligence' comes with a lot of future pain in debugging :D.

The third option was to go with a proper calendar table and standard time intelligence functions. I tried with using the Enhanced Time Intelligence function to create a custom Fiscal Calendar among the Calendar settings; conceptually this is the cleanest, but I’m running into a few issues: keeping prior period logic consistent across different timeframes - sometimes values don't match the expected ones - handling “to-date vs full period” comparisons correctly (this looks so easy but I can't get over it), and dealing with the combination of weekly granularity and a fiscal calendar.

At this point, I am not sure of the trade-off lies between SQL heavylifting, functional data modeling and DAX flexibility. I feel like there are lots of possibilities but also potential increase in complexity along time, if best practices are not adopted. I am relatively 'new' into the BI space so I am still looking to build my foundations and I feel like this will be a strong one in the future as well. :)

So I’m curious how you would approach this. Is there a recommended pattern for handling dynamic timeframe and prior period selection in a single model? How do you typically deal with to-date versus full-period comparisons in this kind of setup?

Thanks in advance.