What’s Like-for-Like (L4L)
to make sure that solely comparable components are in contrast.
Parts might be merchandise, shops, buyer teams, and so on.
Right here, you’ll be able to 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 briefly closed for renovations, repairs, or different causes.
Subsequently, shops might be comparable or non-comparable when evaluating present outcomes with these of the earlier 12 months. Which means when a retailer wasn’t energetic for a particular 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 sure that a report person 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 couple 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 record 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 comprises all shops that aren’t comparable.
As you’ll be able to see, the shops 224 and 226 have a gap date in 2024, 222 has a deadline in 2024, and 222 and 225 have been briefly 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 all the time 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 person should be capable of choose every of the L4L states. When no state is chosen, the information isn’t filtered, and all shops are proven.
- We need to management the outcomes per thirty days. There isn’t 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 information should be filtered within the present 12 months.
For instance, a retailer opens in August 2024. After we look solely on the comparable information for 2025, we shouldn’t see any outcomes for January by July 2025. - The measures used within the experiences shouldn’t be modified to replicate the wanted outcomes.
Getting ready the information
First, I need to create a desk containing all of the months. Moreover, it should embrace 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 hold 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 seems 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 answer
I created a referenced desk from the “Retailer” desk and renamed it to “Bridge_L4L”.
I take away all columns, aside from the StoreKey column.
Subsequent, I want one row for every Retailer and every month.
For this, I add a column for the L4L_Months desk:
After I develop 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 occasions within the record. To have a novel 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 comprises the opening and shutting dates (See Determine 2).
Once more, I develop all columns from the L4L_Months desk, as earlier than.
Once more, every retailer seems a number of occasions within the record. 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 data crucial to pick out the rows with the right L4L state.
I need to accomplish that in line with the opening and shutting dates and examine them to the First- and LastDateOfMonthPY columns utilizing the required 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 information to retain solely the legitimate rows:
The following 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 comprise a null within the L4LKey column.
All these rows are for the shops and months which might be comparable.
Because of this, I exchange all nulls with 1:
Lastly, I eliminated all columns aside from the required columns:
With these steps, I created the Bridge_L4L desk, which might function a filter primarily based 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 spite of everything this, the information mannequin for the concerned tables is the next:
As you’ll be able to see, I’ve solely unidirectional one-to-many relationships, appropriately.
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 totally different situations:
- Opening Shops Firenze and Milan:
Their opening dates have been in Might and in October 2024. As these months don’t comprise Gross sales for the whole month, they’re thought of non-comparable. As you’ll be able to 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 true world. However there might 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 should 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 information for closed shops. This fashion, the outcomes are higher seen.
Easy methods to do it in a different way
This strategy works, however there are different methods to do it. It depends upon the necessities, on which strategy matches your scenario.
- You would possibly transfer this logic from Energy Question to the programming language of your desire, resembling SQL or Python.
- This strategy, 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 approach is likely to be to combine the L4L state into the Reality desk(s). This might keep away from the necessity to have the Bridge desk within the first place.
- You would possibly determine so as to add a historization logic to the Retailer dimension logic and add the L4L state to it. On this case, it’s essential to embrace the L4L hierarchy within the Retailer desk. This is likely to be the most effective strategy as it will embrace a normal SCD2 logic. On the similar time, it’s a extra advanced selection as a result of it provides complexity when making ready the Retailer dimension desk.
The selection of the most effective modeling strategy depends upon the necessities and the talents you will have.
Conclusion
In the present day, I confirmed you easy methods to construct a Like-for-Like answer to match shops throughout years.
The goal of constructing an answer with out modifications to the DAX measures has been achieved. The complete answer is totally data-driven.
This is a vital subject. A DAX-driven logic might be unsustainable, because it introduces the necessity to incorporate further DAX logic into your information mannequin. You all the time want to consider this when including new measures.
Moreover, you could introduce efficiency points, because the code is likely to be extra advanced and probably slower than it will be with out it.
I’m an enormous fan of data-driven options. Typically, they’re higher than having advanced DAX code.
I hope you discovered one thing new and fascinating. See you right here quickly.
References
Right here, a YouTube video by SQLBI about constructing an L4L answer for Manufacturers:
Like in my earlier articles, I exploit the Contoso pattern dataset. You may obtain the ContosoRetailDW Dataset at no cost from Microsoft here.
The Contoso Knowledge can be utilized freely underneath the MIT License, as described in this document. I up to date the dataset to shift the information to up to date dates and eliminated all tables not wanted for this instance.
