What’s Like-for-Like (L4L)
to make sure that solely comparable parts are in contrast.
Parts will be merchandise, shops, buyer teams, and so on.
Right here, you may learn a good explanation of this topic.
Within the present case, I’ll construct an answer for shops.
Shops can open, shut, and even be quickly closed for renovations, repairs, or different causes.
Subsequently, shops will be comparable or non-comparable when evaluating present outcomes with these of the earlier 12 months. Because of this when a retailer wasn’t energetic for a selected interval within the earlier 12 months, it’s non-comparable within the present 12 months, when it was energetic for a similar interval.
L4L will be certain that a report consumer can choose whether or not to incorporate or exclude non-comparable shops.
To pick the L4L state, I create a DIM_L4L desk:
I can use the columns L4L_Test and Cause as a hierarchy in a Slicer or in a Matrix visible.
The Shops
I selected a number of shops from the ContosoRetailDW dataset (Particulars of the ContosoRetailDW dataset within the References part under).
On this case, I selected the shops in Italy.
Right here is the listing of Italian shops with the opening and shutting dates and the assigned L4L states:
On this desk, I added two columns with the end-of-month opening and shutting dates for every retailer.
This desk incorporates all shops that aren’t comparable.
As you may see, the shops 224 and 226 have a gap date in 2024, 222 has a deadline in 2024, and 222 and 225 had been quickly closed in 2023 and 2024.
All different shops might be set to comparable (L4LKey = 1) throughout information preparation for the answer.
What to observe for
So, what are the necessities?
- We at all times look again on the earlier 12 months. In 2025, we take a look at 2024, and in 2024, we take a look at 2023.
- The consumer should have the ability to choose every of the L4L states. When no state is chosen, the info isn’t filtered, and all shops are proven.
- We wish to management the outcomes per thirty days. There is no such thing as a want to alter the each day outcomes.
- When a retailer modifications a state from 1 (Comparable) to a different within the earlier 12 months, the info have to be filtered within the present 12 months.
For instance, a retailer opens in August 2024. Once we look solely on the comparable information for 2025, we shouldn’t see any outcomes for January via July 2025. - The measures used within the stories shouldn’t be modified to mirror the wanted outcomes.
Getting ready the info
First, I need to create a desk containing all of the months. Moreover, it should embody the primary and final dates for every month in each the present and former years.
To do that, I create a desk as a reference from the Date desk in Energy Question.
I maintain solely the next columns and take away all others:
- MonthKey
- MonthKeyPY
- FirstDayOfMonth
- LastDayOfMonth
- FirstDayOfMonthPY
- LastDayOfMonthPY
After that, I take away all duplicates.
The desk L4L_Months appears like this:
Subsequent, I constructed the answer in Energy Question by combining the tables Retailer, L4L_Months, and the desk with the Shops and the opening and shutting dates (Desk title: L4L_Dates).
Constructing the Energy Question resolution
I created a referenced desk from the “Retailer” desk and renamed it to “Bridge_L4L”.
I take away all columns, apart from the StoreKey column.
Subsequent, I would like one row for every Retailer and every month.
For this, I add a column for the L4L_Months desk:
Once I broaden all of the columns from the L4L_Month desk, I get a desk with one row for every mixture of retailer and month:
Now, every retailer seems a number of instances within the listing. To have a singular key-value for every retailer, I add a StoreMonthKey column:
Subsequent, I put together the desk with the shop’s information referred to as “L4L_Dates”.
As for the Bridge_L4L desk, I added the L4L_Months desk to the shops desk, which incorporates the opening and shutting dates (See Determine 2).
Once more, I broaden all columns from the L4L_Months desk, as earlier than.
Once more, every retailer seems a number of instances within the listing. I add the identical distinctive key-value for every retailer (StoreMonthKey):
Textual content.From([StoreKey]) & "_" & Textual content.From([MonthKey])At this level, I’ve all the knowledge obligatory to pick out the rows with the right L4L state.
I need to achieve this in keeping with the opening and shutting dates and examine them to the First- and LastDateOfMonthPY columns utilizing the mandatory logic per L4L-state.
For this, I add a customized 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 falseI title this column “Legitimate”, because it marks the right rows for every L4L-state.
Subsequent, I filter the info to retain solely the legitimate rows:
The subsequent step is to merge the Bridge_L4L desk with the L4L_Dates desk utilizing the beforehand created StoreMonthKey columns:
At this level, I solely want the column L4LKey from the L4L_Dates within the Bridge_L4L desk:
Many of the rows include a null within the L4LKey column.
All these rows are for the shops and months which are comparable.
For that reason, I substitute all nulls with 1:
Lastly, I eliminated all columns apart from the mandatory columns:
With these steps, I created the Bridge_L4L desk, which might function a filter based mostly on the chosen L4L state.
What’s left to do in Energy BI?
Now, I need to place the brand new desk Bridge_L4L between the tables Retailer and the Reality-Desk “Retail Gross sales”.
Then I can add a Relationship from the brand new DIM_L4L to the Bridge_L4L desk.
However so as to add a relationship from the Bridge_L4L desk to the Retail Gross sales reality desk, I need to add the identical StoreMonthKey to the Retail Gross sales desk to uniquely determine the shop for every month.
I do that within the SQL question to retrieve the actual fact information:
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 within the reality desk:
In any case this, the info mannequin for the concerned tables is the next:
As you may see, I’ve solely unidirectional one-to-many relationships, correctly.
The outcomes
After including a Matrix Visible to the Report with the L4L hierarchy, the shops and the months on the columns, I get this for the Gross sales Quantity for 2025:
Let’s take a look at the completely different eventualities:
- Opening Shops Firenze and Milan:
Their opening dates had been in Might and in October 2024. As these months don’t include Gross sales for the complete month, they’re thought of non-comparable. As you may see, the Gross sales change between the Non-Comparable – Opening and the Comparable states. - Closing Retailer Contoso Roma:
The identical image right here. The shop in Rome closed in August 2024. Any outcome after that month is seen as comparable. Keep in mind that these are demo information, and there might be no Gross sales for November and December in the actual world. However there will be prices assigned to the Retailer if you wish to analyze them, for instance, in a P&L report. - Refreshing Retailer Contoso Torino
This retailer closed between March and July 2024. Subsequently, the Gross sales throughout these months have to be thought of as Non-Comparable.
Even when taking a look at 2024, we see that the Rome Retailer is marked accurately as Refresh and all different shops are comparable, besides the Firenze and Milan shops:
The outcomes are precisely what I anticipated.
Keep in mind that I work with demo information, and I deliberately didn’t take away the info for closed shops. This manner, the outcomes are higher seen.
Easy methods to do it in another way
This method works, however there are different methods to do it. It is dependent upon the necessities, on which method suits your state of affairs.
- You may transfer this logic from Energy Question to the programming language of your choice, comparable to SQL or Python.
- This method, with the bridge desk, is nice, because it permits us to set the Relationship between the Retailer and the Bridge desk to bidirectional filtering and conceal the shops that don’t match the chosen L4L state. All Reality tables are linked to the Bridge desk in order that no round dependencies can happen.
- A greater manner is perhaps to combine the L4L state into the Reality desk(s). This could keep away from the necessity to have the Bridge desk within the first place.
- You may determine so as to add a historization logic to the Retailer dimension logic and add the L4L state to it. On this case, you will need to embody the L4L hierarchy within the Retailer desk. This is perhaps the very best method as it will embody a regular SCD2 logic. On the similar time, it’s a extra complicated alternative as a result of it provides complexity when getting ready the Retailer dimension desk.
The selection of the very best modeling method is dependent upon the necessities and the abilities you have got.
Conclusion
As we speak, I confirmed you construct a Like-for-Like resolution to match shops throughout years.
The goal of constructing an answer with out modifications to the DAX measures has been achieved. Your entire resolution is absolutely data-driven.
This is a crucial subject. A DAX-driven logic will be unsustainable, because it introduces the necessity to incorporate further DAX logic into your information mannequin. You at all times want to consider this when including new measures.
Moreover, it’s possible you’ll introduce efficiency points, because the code is perhaps extra complicated and doubtlessly slower than it will be with out it.
I’m a giant fan of data-driven options. Usually, they’re higher than having complicated DAX code.
I hope you realized one thing new and attention-grabbing. See you right here quickly.
References
Right here, a YouTube video by SQLBI about constructing an L4L resolution for Manufacturers:
Like in my earlier articles, I exploit the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset free of charge from Microsoft here.
The Contoso Information can be utilized freely below the MIT License, as described in this document. I up to date the dataset to shift the info to up to date dates and eliminated all tables not wanted for this instance.
