Anyone building Like-for-Like Power BI reports runs into the same advice sooner or later: solve it in DAX. That advice costs you performance and your sanity. If you have ever had to explain to a retail CFO why the year-over-year numbers look off because a store opened in October, you already know why a standard measure will not cover it.
After 14 years of building enterprise solutions, the rule I keep coming back to is that a problem you can solve in the data layer, in SQL or Power Query, does not belong in the semantic layer. So here is the data-driven approach I use for like-for-like (L4L) analysis, the one that keeps DAX short and reports fast.
Why standard YoY lies to you
L4L exists so you only compare things that are actually comparable. Stores open, close and get renovated. Compare 2025 sales against 2024 without accounting for that and you are measuring expansion, not growth. A comparable store is one that was active and fully operational in the same period of the previous year. The standard L4L definitions cover the accounting side of it.
Before refactoring the model, my earlier post on DAX filtering and performance explains what you gain by keeping complex filters out of measures.
The architecture: a bridge table
Instead of a 150-line DAX measure working out comparable status on the fly, build a Bridge_L4L table. That table is what lets a Like-for-Like Power BI model scale.
1. Preparing the month logic
Start with a table that lines up each current month against its previous-year counterpart. In Power Query, reference your Date table and isolate the keys for both sides. You need MonthKey, MonthKeyPY and the start and end dates for each.
2. Building the store-month key
Here is the gotcha: most developers link on StoreKey alone, and that breaks the moment a store changes state mid-year. Use a StoreMonthKey instead. I usually generate it in SQL as a concatenated string or a big integer, something like 224_202501.
SELECT
[F].[StoreKey],
CONCAT(
CONVERT(nvarchar(25), [F].[StoreKey]),
'_',
CONVERT(nvarchar(25), YEAR(DATEADD(yyyy, 16, [F].[DateKey]))),
RIGHT('00' + CONVERT(nvarchar(25), MONTH(DATEADD(yyyy, 16, [F].[DateKey]))), 2)
) AS [StoreMonthKey],
[F].[SalesAmount]
FROM [dbo].[v_FactSales] AS [F];The logic layer in Power Query
In Power Query, expand the Store table against the L4L_Months table so you end up with one row per store per month. Then apply the comparison logic. A store that opened after the FirstDayOfMonthPY, for example, gets flagged as “Non-Comparable – Opening.”
This is where the mess gets handled. A Valid flag in Power Query lets you filter the bridge down to the states that matter, and everything else has its null replaced with 1 for comparable. The result behaves like standard SCD2 logic without paying for full historization.
Relationships: keep them one-to-many
With the bridge in place the model is plain: Store -> Bridge_L4L -> Retail Sales. The StoreMonthKey gives the filter context a direct path to follow. For more on the modeling side, see building enterprise financial models in Power BI.
The payoff is that your sales measure is just SUM(SalesAmount). The hard part already happened upstream, so the measure has nothing left to work out.
If this Like-for-Like Power BI work is eating your dev hours, I can take it on. I have been wrestling with WordPress and BI data models since the 4.x days.
What I learned the hard way
DAX-driven L4L logic does not survive contact with a growing report. Every measure you add later, costs, returns, margin, needs that same logic baked in again. Push it into a bridge table and the whole solution becomes data-driven instead. The SQLBI DAX patterns are the reference on this topic, and their model-independent functions do work, but a clean model beats a clever measure.
