Introduction
to my last article, about constructing the Like-for-Like (L4L) solution based on Power Query:
The answer works as expected for essentially the most part. I showed it to my peers and to some clients.
The feedback was positive, but I’ve got some questions, and the outcomes of my solution weren’t what the person asking expected.
The difficulty
I discovered a problem while calculating the PY value.
Technically, the outcomes are correct, but they aren’t from a user perspective.
Take a look at the next two screenshots, which show two different cases that include the Retail Sales and the Retail Sales PY measures. The outcomes for these two cases can confuse the audience.
Try to identify the problem before continuing to read.
That is the primary case for the Torino store, which was temporarily closed between March and July 2024.
And here is the second case for the Roma store, which was temporarily closed from August to October 2023 and permanently closed in August 2024.
We see these results for the second case:
- The values for the Retail Sales PY measure for “Comparable” stores, but with an interruption between August and October.
- Values for the Retail Sales measure for “Non-Comparable – Closing” stores.
- Values for the Retail Sales PY measure for “Non-Comparable – Refresh” stores.
From a technical standpoint, these results make absolute sense and are correct.
The measures show the proper L4L States for the present period and the previous 12 months.
So, what are the problems?
For the user, they’re very confusing and is not going to match expectations.
Give it some thought from the user’s perspective:
When taking a look at results for specific L4L states, the 2 measures should assign results to the identical L4L state, no matter whether or not they are calculated for the present period or the previous 12 months.
This introduces a brand new complexity to the answer.
The answer
I want a second column for the L4LKey for the previous 12 months.
For the primary L4LKey column, I compare the opening and shutting dates to the monthly dates of the previous 12 months (See the primary article for the main points).
For the second L4LKey_PY column, I need to compare these dates to the monthly dates of the identical 12 months because the opening and closure dates.
The thought is somewhat counterintuitive, however it delivers the result I want.
Please stick with me, and you will notice the way it pans out
First, I attempted solving it in Power Query, as I did in the unique solution. However it didn’t work. I’ll come to the rationale in a minute.
Then, I switched to constructing the Bridge_L4L table in SQL, but the outcomes were unusable again, as I all the time got duplicated rows for the Rome store, as I actually have two rows for the 2 L4L-states for this store:
I actually have one row each for the temporary closure in 2023 and the definitive closure in 2024.
Due to this fact, the join all the time returns two rows, as the shop secret is duplicated.
So, I made a decision to modify to a procedural approach.
I loop through each row within the table containing the opening and shutting stores and apply the states to the table, which has one row per store and month.
I did this by utilizing temporary tables in SQL and the next SQL code:
-- Declare all needed variables
DECLARE @StoreKey int;
DECLARE @OpenDate date;
DECLARE @CloseDate date;
DECLARE @L4LKey int;
-- Create the Cursor to loop through the Stores with each opening, closing, and refresh dates
DECLARE sd CURSOR FOR
SELECT [StoreKey]
,[OpenDate]
,[CloseDate]
,[L4LKey]
FROM #tmp_Store_Dates
-- Order per Closing date, because the procedure must run from the primary (oldest) to the last (newest) row
ORDER BY [CloseDate];
OPEN sd;
-- Get the primary row
FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;
-- Start the loop
WHILE @@FETCH_STATUS = 0
BEGIN
-- Update all rows in keeping with each store based on the L4L status and the respective dates, based on the previous years' dates
UPDATE [#tmp_Stores_Months]
SET [OpenDate] = @OpenDate
,[CloseDate] = @CloseDate
,[L4LKey] = CASE @L4LKey
WHEN 2
THEN IIF(@OpenDate >= [FirstDayOfMonthPY], @L4LKey, NULL)
WHEN 3
THEN IIF(@CloseDate <= [LastDayOfMonthPY], @L4LKey, NULL)
WHEN 4
THEN IIF(@OpenDate >= [FirstDayOfMonthPY] AND @CloseDate <= [LastDayOfMonthPY], @L4LKey, NULL)
ELSE 1
END
WHERE [L4LKey] IS NULL
AND [StoreKey] = @StoreKey;
-- Update based on the same month for the PY calculation
UPDATE [#tmp_Stores_Months]
SET [OpenDate] = @OpenDate
,[CloseDate] = @CloseDate
,[L4LKey_PY] = CASE @L4LKey
WHEN 2
THEN IIF(@OpenDate >= [FirstDayOfMonth], @L4LKey, NULL)
WHEN 3
THEN IIF(@CloseDate <= [LastDayOfMonth], @L4LKey, NULL)
WHEN 4
THEN IIF(@OpenDate >= [FirstDayOfMonth] AND @CloseDate <= [LastDayOfMonth], @L4LKey, NULL)
ELSE 1
END
WHERE [L4LKey_PY] IS NULL
AND [StoreKey] = @StoreKey;
-- Get the subsequent row until all rows are processed
FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;
END
-- Close the Cursor
CLOSE sd;
DEALLOCATE sd;
-- Update the L4LKey and L4LKey_PY in all empty rows
UPDATE #tmp_Stores_Months
SET [L4LKey] = 1
WHERE [L4LKey] IS NULL;
UPDATE #tmp_Stores_Months
SET [L4LKey_PY] = 1
WHERE [L4LKey_PY] IS NULL;The results of the procedure is a table containing one column mapping the L4L states based on the previous 12 months for every month (L4LKey) and one column mapping the L4L states based on the identical 12 months for every month (L4LKey_PY):
The subsequent step is to import the result for this procedure into Power BI and add an extra relationship between the Bridge_4L and the DIM_L4L table for the brand new L4LKey_PY column:
This permits me to manage the calculation for the PY result.
Retail Sales (PY) =
CALCULATE([Retail Sales]
,'Time Intelligence'[Time Measures] = "PY"
,USERELATIONSHIP('Bridge_L4L'[L4LKey_PY], 'DIM_L4L'[L4LKey])
)Now, the outcomes are what is anticipated.
Here, the primary case:
And listed below are the outcomes for the second case:
As you'll be able to see, the PY values are assigned to the identical L4L state because the current-year results.
Now, the user sees consistent results, that are much easier to know.
Conclusion
The extra call of the USERELATIONSHIP() function could be put in a Calculation Item and utilized by all PY measures.
This makes it very easy to make use of with none additional DAX logic.
Anyway, this challenge was relatively easy to unravel. But after I considered a Month-over-Month calculation with the L4L functionality, I spotted it wouldn’t be possible without some DAX code. Possibly, I'll dig into this in a future article.
But this case emphasizes the necessity to use the user’s perspective when designing and testing an answer.
It isn’t enough to make use of a technical perspective; the user’s perspective is way more essential when evaluating the answer’s functionality and results.
For me, this was a really interesting experience and really useful for my future work.
I hope that you just find my approach interesting. Stay tuned for my next piece.
References
That is my previous article on this topic:
Here is the SQLBI article in regards to the like-for-like pattern with a DAX solution based on model-independent UDFs.
Like in my previous articles, I take advantage of the Contoso sample dataset. You may download the ContosoRetailDW Dataset without spending a dime 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.
