Machine Learning

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

nimda March 10, 2026

0 5 8 minutes read

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.

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

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

In the current scenario, I will create a solution for stores.

Stores may open, close, or be temporarily closed for repairs, maintenance, or other reasons.

Therefore, stores may or may not be comparable when comparing the current results with those of the previous year. This means that when a store was closed for a certain period of time in the previous year, it cannot be compared to the current year, when it was open at the same time.

L4L will ensure that the user of the report can choose to include or exclude unmatched stores.

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

Figure 1 – Contents of the DIML4L table (Author's Image)

I can use the L4L_Test and Reason columns as classifications in Slicer or Matrix visualization.

Shops

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

This time, I chose shops in Italy.

Here is a list of Italian stores with opening and closing days and the L4L assigned to them:

Figure 2 – List of Italian stores with opening and closing days, and L4L regions (Author's Image)

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

This table contains all stores that cannot be compared.

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

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

What you can watch

So, what are the requirements?

We always look back at the previous year. In 2025, we're looking at 2024, and in 2024, we're looking at 2023.
The user should be able to select each L4L region. If no condition is selected, the data is not filtered, and all stores are displayed.
We want to control the results by month. No need to change daily results.
If a store changes status from 1 (Comparable) to another in the previous year, the data must be filtered to the current year.
For example, a store opens in August 2024. If we only look at comparable data for 2025, we should not see any results for January to July 2025.
The measures used in the reports should not be changed to reflect the required results.

Preparing the data

First, I have to create a table that contains all the months. Additionally, it should include the start and end dates of each month for both the current and previous year.

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

I keep only the following columns and remove all others:

MonthKey
MonthKeyPY
First Day of the Month
LastDayOfMonth
FirstDayOfMonthPY
LastDayOfMonthPY

After that, I remove all duplicates.

The L4L_Months table looks like this:

Figure 3 – Extract of the L4L_Months table (Author's Image)

Next, I created a solution to the Energy Query by joining the tables Store, L4L_Months, and the table Stores and opening and closing dates (Table name: L4L_Dates).

Building a Solution to the Power Question

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

I remove all the columns, except the StoreKey column.

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

In this case, I add a column to the L4L_Months table:

Figure 4 – Add table L4L_Month to new table Bridge_L4L. (Author's Image)

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

Figure 5 – The Bridge_L4L table after extending the L4L_Months table. Now, each row from the Store table is replicated for each month from the L4L_Months table (Author Image)

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

Figure 6 – Add StoreMonthKey to the Bridge_L4L table to uniquely identify each row in the tagbe (Author Figure)

Next, I prepare a table with store 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 extend all the columns from the L4L_Months table, as before.

Also, each store appears multiple times on 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 needed to select lines with the correct L4L status.

I have to do so according to the opening and closing dates and compare them with the First- and LastDateOfMonthPY columns using the logic required for each L4L-state.

In this case, I added a custom column with the following words:

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 named this column “Active”, as it marks the correct rows for each L4L region.

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

Figure 7 – Sort only valid rows, where opening and closing dates have the correct relationship with FirstDayOnMonthsPY (Author Figure)

The next step is to join the Bridge_L4L table with the L4L_Dates table using the StoreMonthKey columns created earlier:

Figure 8 – Merge the two tables Days_L4L into the Bridge_L4L table (Author's Image)

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

Figure 9 – Expand the L4LKex column in the Bridge_L4L table (Author's Image)

Most of the rows contain null in the L4LKey column.

All of these lines are for comparable stores and months.

For this reason, I replace all nulls with 1:

Figure 10 – Replace all rows with null in the L4LKey column by 1 (Author's Figure)

Finally, I removed all the columns except the required columns:

Figure 11 – Remove all unnecessary columns from the Bridge_L4L table (Author's Figure)

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

What's left to do in Power BI?

Now, I have to put a new table Bridge_L4L between the tables Store and the Fact-Table “Sales Sales”.

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

But to add the relationship from the Bridge_L4L table to the Retail Sales fact table, I have to add the same StoreMonthKey to the Retail Sales table to uniquely identify each month's store.

I do this in a SQL query to get the true 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:

Figure 12 – StoreMonthKey column in fact table (Author's Figure)

After all this, the data model of the tables involved is as follows:

Figure 13 – This is a data model of the tables involved (Author's Image)

As you can see, I have an indirect one-to-many relationship, as it should be.

Results

After adding a Matrix Visual to the report with the L4L system, stores and months in the columns, I get this for 2025 Sales Price:

Figure 14 – The result of comparing the data classification by L4L regions (Author's Figure)

Let's look at different scenarios:

Opening Stores in Florence and Milan:
Their opening dates were May and October 2024. Since these months do not contain sales throughout the month, they are considered non-comparable. As you can see, Sales change between the Non-Comparable – Opening and the Comparable states.
Closing the Store Contoso Roma:
Same picture here. The shop in Rome was closed in August 2024. Any result after that month is seen as a comparison. Keep in mind that this is demo data, and there will be no Sales in 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.
Refreshment Store Contoso Torino
This store is closed between March and July 2024. Therefore, Auctions during these months must be considered non-comparable.

Even if we look at 2024, we see that the Rome Store is correctly marked as Renewal and all other stores are comparable, except for the Firenze and Milan stores:

Figure 15 – 2024 view of the Roman store (222), while temporarily closed in 2023 (Author's Image)

The results were exactly what I expected.

Keep in mind that I am working with demo data, and I intentionally did not remove the data of closed stores. This way, the results are better seen.

How to do it differently

This method works, but there are other ways to do it. It depends on the needs, which method suits your situation.

You can move this logic from Power Query to your favorite programming language, such as SQL or Python.
This method, with the bridge table, is good, as it allows us to set up the Relationship between the Store and the Bridge table in double filtering and hide the stores that do not match the selected L4L condition. All the Truth tables are linked to the Bridge table so that no circular dependencies are possible.
A better approach would be to include the L4L status in the Fact table. This will avoid the need to have a Bridge table in the first place.
You may decide to add a history logic to the dimension Store logic and add the L4L state to it. In this case, you will need to enter the L4L sequence in the Store table. This may be the best approach as it will include the normal logic of SCD2. At the same time, it is a very complicated choice because it adds complexity when preparing the table of dimensions of the Store.

Choosing the best modeling technique depends on your needs and skills.

The conclusion

Today, I'll show you how to create a Like-for-Like solution to compare stores over the years.

The goal of building a solution without changes to the DAX steps was achieved. The entire solution is fully data driven.

This is an important topic. DAX-driven logic may not be sustainable, as it introduces the need to integrate additional DAX logic into your data model. You always need to think about this when adding new steps.

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

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

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

References

Here, SQLBI's YouTube video about building an L4L solution for brands:

As in my previous articles, I am using the Contoso sample data. You can download the ContosoRetailDW Dataset for free from Microsoft here.

Contoso data can be freely used under the MIT license, as described in this document. I updated the data set to change the data to modern dates and remove all the tables that are not needed for this example.

Source link

nimda March 10, 2026

0 5 8 minutes read