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

What’s Like-for-Like (L4L)

to be certain that only comparable elements are compared.

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

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

In the present case, I’ll construct an answer for stores.

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

Subsequently, stores could be comparable or non-comparable when comparing current results with those of the previous yr. Which means when a store wasn’t energetic for a selected period within the previous yr, it’s non-comparable in the present yr, when it was energetic for a similar period.

L4L will be certain that a report user can select whether to incorporate or exclude non-comparable stores.

To pick out the L4L state, I create a DIM_L4L table:

Figure 1 – Content of the DIML4L table (Figure by the Writer)

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

The Stores

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

On this case, I selected the stores in Italy.

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

Figure 2 – List of Italian stores with the opening and shutting dates, along with the L4L states (Figure by the Writer)

On this table, I added two columns with the end-of-month opening and shutting dates for every store.

This table incorporates all stores that aren’t comparable.

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

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

What to look at for

So, what are the necessities?

We at all times look back on the previous yr. In 2025, we have a look at 2024, and in 2024, we have a look at 2023.
The user must give you the chance to pick each of the L4L states. When no state is chosen, the info isn’t filtered, and all stores are shown.
We would like to manage the outcomes monthly. There is no such thing as a need to alter the day by day results.
When a store changes a state from 1 (Comparable) to a different within the previous yr, the info should be filtered in the present yr.
For instance, a store opens in August 2024. After we look only on the comparable data for 2025, we shouldn’t see any results for January through July 2025.
The measures utilized in the reports shouldn’t be modified to reflect the needed results.

Preparing the info

First, I have to create a table containing all of the months. Moreover, it must include the primary and last dates for every month in each the present and former years.

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

I keep only the next columns and take away all others:

MonthKey
MonthKeyPY
FirstDayOfMonth
LastDayOfMonth
FirstDayOfMonthPY
LastDayOfMonthPY

After that, I remove all duplicates.

The table L4L_Months looks like this:

Figure 3 – Extract of the L4L_Months table (Figure by the Writer)

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

Constructing the Power Query solution

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

I remove all columns, aside from the StoreKey column.

Next, I would like one row for every Store and every month.

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

Figure 4 – Add the table L4L_Month to the brand new Bridge_L4L table. (Figure by the Writer)

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

Figure 5 – The table Bridge_L4L after expanding the L4L_Months table. Now, each row from the Store table is multiplied by every month from the L4L_Months table (Figure by the Writer)

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

Figure 6 – Add the StoreMonthKey to the Bridge_L4L table to uniquely discover each row within the tagbe (Figure by the Writer)

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

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

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

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

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

At this point, I actually have all the knowledge essential to pick the rows with the right L4L state.

I have to accomplish that in line with the opening and shutting dates and compare them to the First- and LastDateOfMonthPY columns using the essential logic per L4L-state.

For this, I add a custom column with the next 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”, because it marks the right rows for every L4L-state.

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

Figure 7 – Filter only the valid rows, where the opening and shutting dates are in the right relationship with the First- and LastDayOnMonthsPY (Figure by the Writer)

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

Figure 8 – Merge the 2 tables Dates_L4L into the Birdge_L4L table (Figure by the Writer)

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

Figure 9 – Expand the L4LKex column into the Bridge_L4L table (Figure by the Writer)

A lot of the rows contain a null within the L4LKey column.

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

For that reason, I replace all nulls with 1:

Figure 10 – Replace all rows with a null within the L4LKey column with a 1 (Figure by the Writer)

Lastly, I removed all columns aside from the essential columns:

Figure 11 – Remove all of the unnecessary columns from the Bridge_L4L table (Figure by the Writer)

With these steps, I created the Bridge_L4L table, which might function a filter based on the chosen L4L state.

What’s left to do in Power BI?

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

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

But so as to add a relationship from the Bridge_L4L table to the Retail Sales fact table, I have to add the identical StoreMonthKey to the Retail Sales table to uniquely discover the shop for every month.

I do that within the SQL query to retrieve the very 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 very fact table:

Figure 12 – The StoreMonthKey column in the very fact table (Figure by the Writer)

In any case this, the info model for the involved tables is the next:

Figure 13 – That is the info model for the involved tables (Figure by the Writer)

As you possibly can see, I actually have only unidirectional one-to-many relationships, accurately.

The outcomes

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:

Figure 14 – Result to check the info split by the L4L states (Figuzre by the Writer)

Let’s have a look at the various scenarios:

Opening Stores Firenze and Milan:
Their opening dates were in May and in October 2024. As these months don’t contain Sales for all the month, they're considered non-comparable. As you possibly can see, the Sales switch between the Non-Comparable – Opening and the Comparable states.
Closing Store Contoso Roma:
The identical picture here. The shop in Rome closed in August 2024. Any result after that month is visible as comparable. Do not forget that these are demo data, and there shall be no Sales for November and December in the actual world. But there could be costs assigned to the Store if you would like to analyze them, for instance, in a P&L report.
Refreshing Store Contoso Torino
This store closed between March and July 2024. Subsequently, the Sales during these months should be regarded as Non-Comparable.

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

Figure 15 – 2024 view for the Roma store (222), when it was temporarily closed in 2023 (Figure by the Writer)

The outcomes are exactly what I expected.

Do not forget that I work with demo data, and I intentionally didn’t remove the info for closed stores. This manner, the outcomes are higher visible.

The right way to do it in another way

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

You may move this logic from Power Query to the programming language of your preference, similar to SQL or Python.
This approach, with the bridge table, is great, because it allows us to set the Relationship between the Store and the Bridge table to bidirectional filtering and conceal the stores that don’t fit the chosen L4L state. All Fact tables are linked to the Bridge table in order that no circular dependencies can occur.
A greater way is perhaps to integrate the L4L state into the Fact table(s). This could avoid the necessity to have the Bridge table in the primary place.
You may resolve so as to add a historization logic to the Store dimension logic and add the L4L state to it. On this case, you will need to include the L4L hierarchy within the Store table. This is perhaps the most effective approach as it could include a regular SCD2 logic. At the identical time, it's a more complex selection since it adds complexity when preparing the Store dimension table.

The selection of the most effective modeling approach depends upon the necessities and the abilities you will have.

Conclusion

Today, I showed you construct a Like-for-Like solution to check stores across years.

The goal of constructing an answer without changes to the DAX measures has been achieved. The complete solution is fully data-driven.

That is a very important topic. A DAX-driven logic could be unsustainable, because it introduces the necessity to incorporate additional DAX logic into your data model. You mostly have to take into consideration this when adding latest measures.

Moreover, you could introduce performance issues, because the code is perhaps more complex and potentially slower than it could be without it.

I’m an enormous fan of data-driven solutions. Normally, they're higher than having complex DAX code.

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

References

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

Like in my previous articles, I take advantage of the Contoso sample dataset. You'll be able to download the ContosoRetailDW Dataset totally free from Microsoft here.

The Contoso Data could be used freely under the MIT License, as described on this document. I updated the dataset to shift the info to contemporary dates and removed all tables not needed for this instance.

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

What’s Like-for-Like (L4L)

The Stores

What to look at for

Preparing the info

Constructing the Power Query solution

What’s left to do in Power BI?

The outcomes

The right way to do it in another way

Conclusion

References

What are your thoughts on this topic?
Let us know in the comments below.

Share this article

Recent posts

How NVIDIA Builds Open Data for AI

How Joseph Paradiso’s sensing innovations bridge the humanities, medicine, and ecology

NVIDIA RTX Innovations Are Powering the Next Era of Game Development

Hybrid Neuro-Symbolic Fraud Detection: Guiding Neural Networks with Domain Rules

How AI Innovation Is Paving the Path to AGI — Google DeepMind

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

What’s Like-for-Like (L4L)

The Stores

What to look at for

Preparing the info

Constructing the Power Query solution

What’s left to do in Power BI?

The outcomes

The right way to do it in another way

Conclusion

References

What are your thoughts on this topic? Let us know in the comments below.

Share this article

Recent posts

What are your thoughts on this topic?
Let us know in the comments below.