Like-for-Like Sales in DAX: Comparable Stores and Weeks

Everything difficult about like-for-like sits in the word only: which stores qualify, which of their weeks count, and whether the same set is used on both sides of the comparison. Get that wrong and the measure still returns a number. It is the number that makes a bad year look flat.

What Like-for-Like Sales Actually Measure

The purpose is to separate trading performance from estate changes. A chain that opened twenty stores will grow total sales whatever happens in the shops it already had. Like-for-like strips the new ones out and answers a narrower question: did the existing business sell more.

There is no standard definition. Each retailer sets its own and states it in its filings, which is why two chains reporting LFL plus three percent are not necessarily comparable with each other. Macy's, answering an SEC comment on exactly this, described its rule as sales from stores in operation throughout both periods being compared, with remodels, expansions and relocations staying in the calculation unless the store closed for any significant period, closed stores removed once the closure is announced, and all internet sales included.

Three decisions hide in that one sentence, and the model has to answer all three: how long a store must have traded to qualify at all, what happens to a store that was shut for a refit in the middle of the comparison, and whether e-commerce is in or out — with a third answer needed where the chain has click-and-collect. None of these has a right answer. They have an answer the business has chosen, and it has to be written down, because a measure that silently encodes one of them is a measure nobody can audit.

Store-Based and Store-Period: Two Ways to Choose Comparable Stores

The two methods differ in what they exclude, and the choice changes the number. Store-based treats eligibility as a property of the store: it qualifies when it has enough trading history to cover both periods in full, thirteen months for a month-on-month read, the current year plus the whole prior year for year to date. A store that opened midway through last year is dropped entirely, including the months where a perfectly good comparison exists.

Store-period decides eligibility per store and per week instead. A store that opened in week 5 of last year contributes weeks 5 to 52 and nothing earlier. Nothing usable is thrown away, and the comparison stays honest because the same weeks are excluded on both sides.

Store-period is the better method and the one worth building. It is also the one that needs a model behind it, because eligibility is no longer an attribute of the store: it is a fact about a store and a week.

The Comparable-Store Rule Belongs in a Table

Comparable-store eligibility is a business rule with exceptions: refits, relocations, announced closures, a store that traded four days of its opening week. Those exceptions arrive by email rather than by formula, so the rule belongs in a maintained table that somebody owns.

Build it in Power Query or upstream in the warehouse. Every row in it is a promise: this store, this week, has a valid counterpart last year. A store-week belongs in the table when the store traded in that week and also traded in its counterpart week last year. Deriving traded from the sales fact is the quick version and it is wrong at the edges, because a store that was open with no sales in a quiet week looks closed, so where a store trading calendar exists, use it.

The DAX for Like-for-Like Sales

With that table in the model the measures are short, and the whole point is that both of them read the same rows.

Sales LFL =
VAR Pairs =
    SELECTCOLUMNS (
        'Comparable Store Weeks',
        "@Store", 'Comparable Store Weeks'[Store Key],
        "@Week", 'Comparable Store Weeks'[Week Index]
    )
RETURN
    CALCULATE (
        [Sales Value],
        KEEPFILTERS ( TREATAS ( Pairs, Store[Store Key], 'Retail Calendar'[Week Index] ) )
    )

Sales LFL LY =
VAR Pairs =
    SELECTCOLUMNS (
        'Comparable Store Weeks',
        "@Store", 'Comparable Store Weeks'[Store Key],
        "@Week", 'Comparable Store Weeks'[LY Week Index]
    )
RETURN
    CALCULATE (
        [Sales Value],
        REMOVEFILTERS ( 'Retail Calendar' ),
        TREATAS ( Pairs, Store[Store Key], 'Retail Calendar'[Week Index] )
    )

LFL % =
DIVIDE ( [Sales LFL] - [Sales LFL LY], [Sales LFL LY] )

The two measures are deliberately near-identical: the only difference is which column of the same filtered rows they project. The comparable table is filtered by whatever weeks the user has selected before either measure runs, so the last-year side follows the selection instead of being recomputed from dates.

REMOVEFILTERS on the calendar in the second measure is what lets last year's weeks through. Without it the filter from the slicer and the filter from TREATAS intersect to nothing and the measure returns blank, which is the single most common way this pattern is broken.

Why Like-for-Like Needs Aligned Weeks

Comparing week 42 with week 42 only works if the two weeks are the same part of the season. On an ordinary calendar they are not: dates drift by a day or two each year, Easter moves by weeks, and a 53-week year shifts everything after it by one.

This is why LY Week Index is read from the retail calendar rather than written as Week Index minus 52 inside the measure. The calendar holds the alignment, including the restatement across a 53-week year, and the like-for-like measures inherit it. A like-for-like built on calendar dates carries an artefact nobody can separate from trading.

Mistakes in Like-for-Like Measures

• Filtering the current period and not last year. This year counts comparable stores only, last year counts everything, and the growth number is pure estate change.
• Deciding eligibility from a measure instead of a table. It works until somebody slices by category, at which point a store with no sales in that category looks closed and silently leaves the comparison.
• Subtracting 52 inside the measure. Correct until the 53-week year, then quietly wrong for twelve months.
• Dropping a whole store because it opened midway through last year. The store-based method throws away months of valid comparison that the store-period method keeps.
• Leaving e-commerce undecided. Including online in one year and not the other produces growth nobody can explain, and click-and-collect needs a decision of its own.
• Reporting the percentage without the store count. LFL plus 3% across 180 of 214 stores is a fact; LFL plus 3% is a claim.