Building a Like-for-Like solution for Stores in Power BI

What is Like-for-Like (L4L)

to ensure that only comparable elements are compared.

Elements can be products, stores, customer groups, etc.

Here, you can read a good explanation of this topic.

In the current case, I will build a solution for stores.

Stores can open, close, or even be temporarily closed for renovations, repairs, or other reasons.

Therefore, stores can be comparable or non-comparable when comparing current results with those of the previous year. This means that when a store wasn’t active for a specific period in the previous year, it is non-comparable in the current year, when it was active for the same period.

L4L will ensure that a report user can select whether to include or exclude non-comparable stores.

To select the L4L state, I create a DIM_L4L table:

I can use the columns L4L_Test and Reason as a hierarchy in a Slicer or in a Matrix visual.

The Stores

I chose a few stores from the ContosoRetailDW dataset (Details of the ContosoRetailDW dataset in the References section below).

In this case, I chose the stores in Italy.

Here is the list of Italian stores with the opening and closing dates and the assigned L4L states:

In this table, I added two columns with the end-of-month opening and closing dates for each store.

This table contains all stores that are not comparable.

As you can see, the stores 224 and 226 have an opening date in 2024, 222 has a closing date in 2024, and 222 and 225 were temporarily closed in 2023 and 2024.

All other stores will be set to comparable (L4LKey = 1) during data preparation for the solution.

What to watch for

So, what are the requirements?

1. We always look back on the previous year. In 2025, we look at 2024, and in 2024, we look at 2023.
2. The user must be able to select each of the L4L states. When no state is selected, the data isn’t filtered, and all stores are shown.
3. We want to control the results per month. There is no need to change the daily results.
4. When a store changes a state from 1 (Comparable) to another in the previous year, the data must be filtered in the current year.
   For example, a store opens in August 2024. When we look only at the comparable data for 2025, we shouldn’t see any results for January through July 2025.
5. The measures used in the reports shouldn’t be changed to reflect the needed results.

Preparing the data

First, I must create a table containing all the months. Additionally, it must include the first and last dates for each month in both the current and previous years.

To do this, I create a table as a reference from the Date table in Power Query.

I keep only the following columns and remove all others:

• MonthKey
• MonthKeyPY
• FirstDayOfMonth
• LastDayOfMonth
• FirstDayOfMonthPY
• LastDayOfMonthPY

After that, I remove all duplicates.

The table L4L_Months looks like this:

Next, I built the solution in Power Query by combining the tables Store, L4L_Months, and the table with the Stores and the opening and closing dates (Table name: L4L_Dates).

Building the Power Query solution

I created a referenced table from the “Store” table and renamed it to “Bridge_L4L”.

I remove all columns, except for the StoreKey column.

Next, I need one row for each Store and each month.

For this, I add a column for the L4L_Months table:

When I expand all the columns from the L4L_Month table, I get a table with one row for each combination of store and month:

Now, each store appears multiple times in the list. To have a unique key-value for each store, I add a StoreMonthKey column:

Next, I prepare the table with the store’s data called “L4L_Dates”.

As for the Bridge_L4L table, I added the L4L_Months table to the stores table, which contains the opening and closing dates (See Figure 2).

Again, I expand all columns from the L4L_Months table, as before.

Again, each store appears multiple times in the list. I add the same unique key-value for each store (StoreMonthKey):

Text.From([StoreKey]) & "_" & Text.From([MonthKey])

At this point, I have all the information necessary to select the rows with the correct L4L state.

I must do so according to the opening and closing dates and compare them to the First- and LastDateOfMonthPY columns using the necessary logic per L4L-state.

For this, I add a custom column with the following expression:

if [L4LKey] = 2 and
       [OpenDate] >= [FirstDayOfMonthPY]
   then true
      else if [L4LKey] = 3 and
          [CloseDate] <= [LastDayOfMonthPY]
          then true
              else if [L4LKey] = 4 and ([OpenDate] >= [FirstDayOfMonthPY] and [CloseDate] <= [LastDayOfMonthPY])
              then true
          else false

I name this column “Valid”, as it marks the correct rows for each L4L-state.

Next, I filter the data to retain only the valid rows:

The next step is to merge the Bridge_L4L table with the L4L_Dates table using the previously created StoreMonthKey columns:

At this point, I only need the column L4LKey from the L4L_Dates in the Bridge_L4L table:

Most of the rows contain a null in the L4LKey column.

All these rows are for the stores and months that are comparable.

For this reason, I replace all nulls with 1:

Lastly, I removed all columns except for the necessary columns:

With these steps, I created the Bridge_L4L table, which can serve as a filter based on the selected L4L state.

What’s left to do in Power BI?

Now, I must place the new table Bridge_L4L between the tables Store and the Fact-Table “Retail Sales”.

Then I can add a Relationship from the new DIM_L4L to the Bridge_L4L table.

But to add a relationship from the Bridge_L4L table to the Retail Sales fact table, I must add the same StoreMonthKey to the Retail Sales table to uniquely identify the store for each month.

I do this in the SQL query to retrieve the fact data:

SELECT [F].[SaleLineCounter]    AS  [Sale Line Counter]
        ,CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey]))     AS  [DateKey]
        ,[F].[channelKey]
        ,[F].[StoreKey]
        ,CONCAT(CONVERT(nvarchar(25), [F].[StoreKey])
                ,'_'
                ,CONVERT(nvarchar(25), YEAR(CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey]))))
                ,RIGHT('00' + CONVERT(nvarchar(25), MONTH(CONVERT(date, DATEADD(yyyy, 16, [F].[DateKey])))), 2)
                )               AS  [StoreMonthKey]
        ,[F].[ProductKey]
        ,[F].[PromotionKey]
        ,[F].[CurrencyKey]
        ,[F].[UnitCost]
        ,[F].[UnitPrice]
        ,[F].[SalesQuantity]
        ,[F].[ReturnQuantity]
        ,[F].[ReturnAmount]
        ,[F].[DiscountQuantity]
        ,[F].[DiscountAmount]
        ,[F].[TotalCost]
        ,[F].[SalesAmount]
        ,[F].[DateKeyYear]
    FROM [dbo].[v_FactSales]    AS  [F];

Now I get this column in the fact table:

After all this, the data model for the involved tables is the following:

As you can see, I have only unidirectional one-to-many relationships, as it should be.

The results

After adding a Matrix Visual to the Report with the L4L hierarchy, the stores and the months on the columns, I get this for the Sales Amount for 2025:

Let’s look at the different scenarios:

• Opening Stores Firenze and Milan:
  Their opening dates were in May and in October 2024. As these months don’t contain Sales for the entire month, they are considered non-comparable. As you can see, the Sales switch between the Non-Comparable – Opening and the Comparable states.
• Closing Store Contoso Roma:
  The same picture here. The store in Rome closed in August 2024. Any result after that month is visible as comparable. Remember that these are demo data, and there will be no Sales for November and December in the real world. But there can be costs assigned to the Store if you want to analyze them, for example, in a P&L report.
• Refreshing Store Contoso Torino
  This store closed between March and July 2024. Therefore, the Sales during these months must be considered as Non-Comparable.

Even when looking at 2024, we see that the Rome Store is marked correctly as Refresh and all other stores are comparable, except the Firenze and Milan stores:

The results are exactly what I expected.

Remember that I work with demo data, and I intentionally didn’t remove the data for closed stores. This way, the results are better visible.

How to do it differently

This approach works, but there are other ways to do it. It depends on the requirements, on which approach fits your situation.

• You might move this logic from Power Query to the programming language of your preference, such as SQL or Python.
• This approach, with the bridge table, is great, as it allows us to set the Relationship between the Store and the Bridge table to bidirectional filtering and hide the stores that don’t fit the selected L4L state. All Fact tables are linked to the Bridge table so that no circular dependencies can occur.
• A better way might be to integrate the L4L state into the Fact table(s). This would avoid the need to have the Bridge table in the first place.
• You might decide to add a historization logic to the Store dimension logic and add the L4L state to it. In this case, you must include the L4L hierarchy in the Store table. This might be the best approach as it would include a standard SCD2 logic. At the same time, it is a more complex choice because it adds complexity when preparing the Store dimension table.

The choice of the best modeling approach depends on the requirements and the skills you have.

Conclusion

Today, I showed you how to build a Like-for-Like solution to compare stores across years.

The target of building a solution without changes to the DAX measures has been achieved. The entire solution is fully data-driven.

This is an important topic. A DAX-driven logic can be unsustainable, as it introduces the need to incorporate additional DAX logic into your data model. You always need to think about this when adding new measures.

Additionally, you may introduce performance issues, as the code might be more complex and potentially slower than it would be without it.

I’m a big fan of data-driven solutions. In most cases, they are better than having complex DAX code.

I hope you learned something new and interesting. See you here soon.

References

Here, a YouTube video by SQLBI about building an L4L solution for Brands:

Like in my previous articles, I use the Contoso sample dataset. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be used freely under the MIT License, as described in this document. I updated the dataset to shift the data to contemporary dates and removed all tables not needed for this example.