What’s Like-for-Like (L4L)
to make sure that solely comparable components are in contrast.
Parts will be merchandise, shops, buyer teams, and so forth.
Right here, you possibly can learn a good rationalization of this subject.
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.
Due to this fact, shops will be comparable or non-comparable when evaluating present outcomes with these of the earlier 12 months. Which means that when a retailer wasn’t lively for a selected interval within the earlier 12 months, it’s non-comparable within the present 12 months, when it was lively for a similar interval.
L4L will make sure that a report consumer can choose whether or not to incorporate or exclude non-comparable shops.
To pick out 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 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 accommodates all shops that aren’t comparable.
As you possibly can see, the shops 224 and 226 have a gap date in 2024, 222 has a time limit in 2024, and 222 and 225 have been quickly closed in 2023 and 2024.
All different shops will likely 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 consumer should be capable to 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 monthly. There isn’t any want to vary the each day outcomes.
- When a retailer adjustments 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. Once we look solely on the comparable information for 2025, we shouldn’t see any outcomes for January by means of July 2025. - The measures used within the reviews shouldn’t be modified to replicate the wanted outcomes.
Making ready the information
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 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 known as “L4L_Dates”.
As for the Bridge_L4L desk, I added the L4L_Months desk to the shops desk, which accommodates 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 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 knowledge mandatory to pick out the rows with the right L4L state.
I need to accomplish that in response to the opening and shutting dates and evaluate 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 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:
A lot of the rows include 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 apart from the required 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 Truth-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 truth desk, I need to add the identical StoreMonthKey to the Retail Gross sales desk to uniquely establish 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 truth desk:
In spite of everything this, the information mannequin for the concerned tables is the next:
As you possibly can 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 completely different situations:
- Opening Shops Firenze and Milan:
Their opening dates have been in Might and in October 2024. As these months don’t include Gross sales for your entire month, they’re thought-about non-comparable. As you possibly can see, the Gross sales swap 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 consequence after that month is seen as comparable. Do not forget that these are demo information, and there will likely 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. Due to this fact, the Gross sales throughout these months should be thought-about as Non-Comparable.
Even when 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.
Do not forget 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.
Tips on how to do it in another way
This method works, however there are different methods to do it. It is determined by the necessities, on which method matches your scenario.
- You would possibly transfer this logic from Energy Question to the programming language of your desire, reminiscent of 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 Truth tables are linked to the Bridge desk in order that no round dependencies can happen.
- A greater manner is likely to be to combine the L4L state into the Truth desk(s). This may keep away from the necessity to have the Bridge desk within the first place.
- You would possibly resolve so as to add a historization logic to the Retailer dimension logic and add the L4L state to it. On this case, you should embody the L4L hierarchy within the Retailer desk. This is likely to be the very best method as it will embody an ordinary SCD2 logic. On the similar time, it’s a extra advanced alternative as a result of it provides complexity when getting ready the Retailer dimension desk.
The selection of the very best modeling method is determined by the necessities and the talents you have got.
Conclusion
Right this moment, I confirmed you the best way to construct a Like-for-Like resolution to match shops throughout years.
The goal of constructing an answer with out adjustments to the DAX measures has been achieved. The complete resolution is totally 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 all the time want to consider this when including new measures.
Moreover, it’s possible you’ll 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. Normally, 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 resolution for Manufacturers:
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset free of charge from Microsoft right here.
The Contoso Information can be utilized freely underneath the MIT License, as described on this doc. I up to date the dataset to shift the information to up to date dates and eliminated all tables not wanted for this instance.
