We need to talk about Like-for-Like Power BI modeling. For some reason, the standard advice in the business intelligence world has become “solve it in DAX,” and it’s killing performance and sanity. If you’ve ever tried to explain to a retail CFO why their year-over-year sales look “off” because of a store opening in October, you know exactly why standard measures aren’t enough.
In my 14+ years of building enterprise-grade solutions, I’ve learned one hard truth: if you can solve a problem in the data layer (SQL or Power Query), don’t force it into the semantic layer. Today, I’m breaking down a robust, data-driven approach to Like-for-Like (L4L) analysis that keeps your DAX clean and your reports fast.
Why Standard YoY is Lying to You
L4L is a mechanism to ensure that only comparable elements are compared. Stores open, close, and undergo renovations. If you compare 2025 sales to 2024 without accounting for these changes, you aren’t measuring growth; you’re measuring expansion. A “Comparable” store is one that was active and fully operational during the same period in the previous year. You can find a deeper dive into the standard L4L definitions here.
Before we refactor the model, check out my previous post on DAX Filtering and performance to see why avoiding complex filters in measures is a massive win.
The Architecture: The Bridge Table Strategy
Instead of a 150-line DAX measure that calculates comparable status on the fly, we’re going to build a Bridge_L4L table. This is the heart of a Like-for-Like Power BI solution that scales.
1. Preparing the Month Logic
First, we need a table that aligns current months with their previous-year counterparts. In Power Query, reference your Date table and isolate the keys for current and previous year months. We need MonthKey, MonthKeyPY, and the start/end dates for both.
2. Building the Store-Month Unique Key
Here is the gotcha: most devs try to link on just StoreKey. That fails the moment a store changes state mid-year. You need a StoreMonthKey. I usually generate this as a concatenated string or a big integer in SQL. It looks 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: Power Query Transformation
In Power Query, we expand our Store table against the L4L_Months table. This gives us one row per store, per month. We then apply our comparison logic. For example, if a store opened after the FirstDayOfMonthPY, it’s flagged as “Non-Comparable – Opening.”
This is where we handle the mess. By setting a Valid flag in Power Query, we filter the bridge table down to only the relevant states. We then replace nulls with a value of 1 (Comparable) for everything else. This mimics a standard SCD2 logic without the overhead of full historization.
Relationships: One-to-Many or Bust
Once the bridge is built, the model is simple: Store -> Bridge_L4L -> Retail Sales. By using the StoreMonthKey, we create a direct path for the filter context. For more on advanced modeling, see building enterprise financial models in Power BI.
The beauty of this? Your Sales measure is just SUM(SalesAmount). No complex DAX, no race conditions, just clean performance.
Look, if this Like-for-Like Power BI stuff is eating up your dev hours, let me handle it. I’ve been wrestling with WordPress and BI data models since the 4.x days.
The Hard Lesson
A DAX-driven logic is unsustainable. Every time you add a new measure (Costs, Returns, Margin), you have to bake the L4L logic into it. By pushing the logic into a bridge table, you make your solution fully data-driven. If you’re looking for the absolute gold standard on this, check the SQLBI DAX patterns—but remember, their model-independent functions are great, but a clean model is better.
