Like-for-like PY store analysis is where technically correct turns into functionally broken. Fourteen years of building data pipelines taught me that one the hard way. If your dashboard marks a store as ‘Comparable’ this year while its previous year (PY) metrics are calculated against a ‘Non-Comparable’ state, nobody is going to admire your clean schema. The client will just say the report is wrong.
The case that put me here was a retail store that closed temporarily for a refresh. The data was accurate. It was also unusable, because the statuses did not line up across the two periods. Anyone who has hit this knows standard joins fall apart once a store carries several events in the same history: an opening, a closing, a refresh.
Where the naive join breaks
Most people reach for Power Query or a plain SQL join and treat the store key as a unique identifier. That holds until a store collects more than one lifecycle event. Our Rome store had a temporary closure in 2023 and a permanent one in 2024, which is two rows behind one key, and a standard join turns that into a Cartesian product that makes the like-for-like PY comparison worthless.
I have written before about robust like-for-like Power BI models, but the PY side of it needs something more procedural. The fix is a separate L4L key for the previous year, one that forces the calculation to read the store’s current status context.
The procedural SQL fix
When set-based logic falls over because of row duplication, I go procedural: a SQL cursor that walks the store dates and updates a mapping table. Every store-month combination comes out with the status key it needs, without a stack of nested joins to maintain.
-- The Senior Dev's Workaround: Procedural Status Mapping
DECLARE @StoreKey INT, @OpenDate DATE, @CloseDate DATE, @L4LKey INT;
DECLARE sd CURSOR FOR
SELECT StoreKey, OpenDate, CloseDate, L4LKey
FROM #tmp_Store_Dates
ORDER BY CloseDate;
OPEN sd;
FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Update based on the current L4L state for the PY calculation
UPDATE [#tmp_Stores_Months]
SET [L4LKey_PY] = CASE @L4LKey
WHEN 2 THEN IIF(@OpenDate >= [FirstDayOfMonth], @L4LKey, NULL)
WHEN 3 THEN IIF(@CloseDate <= [LastDayOfMonth], @L4LKey, NULL)
ELSE 1
END
WHERE [L4LKey_PY] IS NULL AND [StoreKey] = @StoreKey;
FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;
END
CLOSE sd;
DEALLOCATE sd;Wiring it into DAX time intelligence
Once L4LKey_PY exists in the backend, the Power BI side gets much simpler. The tangle of filter logic goes away and the USERELATIONSHIP function carries it, so a measure can switch between this year’s status and last year’s.
Retail Sales (PY) =
CALCULATE(
[Retail Sales],
'Time Intelligence'[Time Measures] = "PY",
USERELATIONSHIP('Bridge_L4L'[L4LKey_PY], 'DIM_L4L'[L4LKey])
)Now when someone filters for “Comparable” stores, both sides of the comparison agree on what that means. “Refresh” stores stop leaking into “Permanent” results, which was the confusion that started all of this. The model ends up matching the way the business talks about its own stores.
My guide on escaping the SQL jungle covers the transformation side of this. For the L4L patterns themselves, SQLBI has good documentation on model-independent functions.
If like-for-like PY store analysis is eating your dev hours, I can take it on. I have been wrestling with WordPress since the 4.x days.
How I test this
A DAX measure that returns a number is not the same as a DAX measure that is right. I cross-check against the ugly cases first, mid-month closures above all. Procedural SQL looks dirty next to a tidy set-based query, and it is the version that keeps the report standing when the source data misbehaves. Put the effort into the architecture rather than into being clever in Power Query.
