Following Up on Like-for-Like for Stores: Handling PY

The solution works as expected for 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 results of my solution weren’t what the person asking expected.

The issue

I discovered an issue while calculating the PY value.

Technically, the results are correct, but they aren’t from a user perspective.

Look at the following two screenshots, which show two different cases that include the Retail Sales and the Retail Sales PY measures. The results for these two cases can confuse the audience.

Try to spot the issue before continuing to read.

This is the first 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:

1. The values for the Retail Sales PY measure for “Comparable” stores, but with an interruption between August and October.
2. Values for the Retail Sales measure for “Non-Comparable – Closing” stores.
3. Values for the Retail Sales PY measure for “Non-Comparable – Refresh” stores.

From a technical point of view, these results make absolute sense and are correct.

The measures show the correct L4L States for the current period and the previous year.

So, what are the issues?

For the user, they are very confusing and will not match expectations.

Think about it from the user’s perspective:

When looking at results for specific L4L states, the two measures should assign results to the same L4L state, regardless of whether they are calculated for the current period or the previous year.

This introduces a new complexity to the solution.

The solution

I need a second column for the L4LKey for the previous year.

For the first L4LKey column, I compare the opening and closing dates to the monthly dates of the previous year (See the first article for the details).

For the second L4LKey_PY column, I must compare these dates to the monthly dates of the same year as the opening and closure dates.

The idea is somewhat counterintuitive, but it delivers the result I need.
Please stay with me, and you will see how it pans out

First, I tried solving it in Power Query, as I did in the original solution. But it didn’t work. I will come to the reason in a minute.

Then, I switched to building the Bridge_L4L table in SQL, but the results were unusable again, as I always got duplicated rows for the Rome store, as I have two rows for the two L4L-states for this store:

I have one row each for the temporary closure in 2023 and the definitive closure in 2024.

Therefore, the join always returns two rows, as the store key is duplicated.

So, I decided to switch to a procedural approach.

I loop through each row in the table containing the opening and closing stores and apply the states to the table, which has one row per store and month.

I did this by using temporary tables in SQL and the following 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, as the procedure must run from the first (oldest) to the last (newest) row
            ORDER BY [CloseDate];

OPEN sd;

-- Get the first row
FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;

-- Start the loop
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Update all rows according to 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 next 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 result of the procedure is a table containing one column mapping the L4L states based on the previous year for each month (L4LKey) and one column mapping the L4L states based on the same year for each month (L4LKey_PY):

The next step is to import the result for this procedure into Power BI and add an additional relationship between the Bridge_4L and the DIM_L4L table for the new L4LKey_PY column:

This allows me to control 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 results are what is expected.

Here, the first case:

And here are the results for the second case:

As you can see, the PY values are assigned to the same L4L state as the current-year results.

Now, the user sees consistent results, which are much easier to understand.

Conclusion

The additional call of the USERELATIONSHIP() function can be put in a Calculation Item and used by all PY measures.

This makes it very easy to use without any additional DAX logic.

Anyway, this challenge was relatively easy to solve. But when I considered a Month-over-Month calculation with the L4L functionality, I realized it wouldn’t be possible without some DAX code. Possibly, I will dig into this in a future article.

But this case emphasizes the need to use the user’s perspective when designing and testing a solution.

It isn’t enough to use a technical perspective; the user’s perspective is much more important when evaluating the solution’s functionality and results.

For me, this was a very interesting experience and very useful for my future work.

I hope that you find my approach interesting. Stay tuned for my next piece.

References

This is my previous article on this topic: