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

The answer works as anticipated for probably the most half. I confirmed it to my friends and to some purchasers.

The suggestions was optimistic, however I’ve obtained some questions, and the outcomes of my answer weren’t what the particular person asking anticipated.

The difficulty

I found a problem whereas calculating the PY worth.

Technically, the outcomes are right, however they aren’t from a person perspective.

Take a look at the next two screenshots, which present two totally different instances that embody the Retail Gross sales and the Retail Gross sales PY measures. The outcomes for these two instances can confuse the viewers.

Attempt to spot the difficulty earlier than persevering with to learn.

That is the primary case for the Torino retailer, which was briefly closed between March and July 2024.

And right here is the second case for the Roma retailer, which was briefly closed from August to October 2023 and completely closed in August 2024.

We see these outcomes for the second case:

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

From a technical standpoint, these outcomes make absolute sense and are right.

The measures present the proper L4L States for the present interval and the earlier 12 months.

So, what are the problems?

For the person, they’re very complicated and won’t match expectations.

Give it some thought from the person’s perspective:

When outcomes for particular L4L states, the 2 measures ought to assign outcomes to the identical L4L state, no matter whether or not they’re calculated for the present interval or the earlier 12 months.

This introduces a brand new complexity to the answer.

The answer

I want a second column for the L4LKey for the earlier 12 months.

For the primary L4LKey column, I examine the opening and shutting dates to the month-to-month dates of the earlier 12 months (See the primary article for the small print).

For the second L4LKey_PY column, I need to examine these dates to the month-to-month dates of the identical 12 months because the opening and closure dates.

The concept is considerably counterintuitive, nevertheless it delivers the outcome I want.
Please stick with me, and you will note the way it pans out

First, I attempted fixing it in Energy Question, as I did within the authentic answer. However it didn’t work. I’ll come to the rationale in a minute.

Then, I switched to constructing the Bridge_L4L desk in SQL, however the outcomes have been unusable once more, as I all the time obtained duplicated rows for the Rome retailer, as I’ve two rows for the 2 L4L-states for this retailer:

I’ve one row every for the short-term closure in 2023 and the definitive closure in 2024.

Due to this fact, the be a part of all the time returns two rows, as the shop secret’s duplicated.

So, I made a decision to change to a procedural strategy.

I loop by way of every row within the desk containing the opening and shutting shops and apply the states to the desk, which has one row per retailer and month.

I did this through the use of short-term tables in SQL and the next SQL code:

-- Declare all wanted variables
DECLARE @StoreKey       int;
DECLARE @OpenDate       date;
DECLARE @CloseDate      date;
DECLARE @L4LKey         int;

-- Create the Cursor to loop by way of the Shops with every opening, closing, and refresh dates
DECLARE sd CURSOR FOR
    SELECT [StoreKey]
            ,[OpenDate]
            ,[CloseDate]
            ,[L4LKey]
        FROM #tmp_Store_Dates
            -- Order per Deadline, because the process should run from the primary (oldest) to the final (latest) row
            ORDER BY [CloseDate];

OPEN sd;

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

-- Begin the loop
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Replace all rows in line with every retailer based mostly on the L4L standing and the respective dates, based mostly on the earlier 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;

-- Replace based mostly on the identical 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 following row till all rows are processed
    FETCH NEXT FROM sd INTO @StoreKey, @OpenDate, @CloseDate, @L4LKey;

END

-- Shut the Cursor
CLOSE sd;
DEALLOCATE sd;

-- Replace 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 process is a desk containing one column mapping the L4L states based mostly on the earlier 12 months for every month (L4LKey) and one column mapping the L4L states based mostly on the identical 12 months for every month (L4LKey_PY):

The subsequent step is to import the outcome for this process into Energy BI and add an extra relationship between the Bridge_4L and the DIM_L4L desk for the brand new L4LKey_PY column:

This enables me to regulate the calculation for the PY outcome.

Retail Gross 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.

Right here, the primary case:

And listed below are the outcomes for the second case:

As you possibly can see, the PY values are assigned to the identical L4L state because the current-year outcomes.

Now, the person sees constant outcomes, that are a lot simpler to know.

Conclusion

The extra name of the USERELATIONSHIP() operate may be put in a Calculation Merchandise and utilized by all PY measures.

This makes it very straightforward to make use of with none further DAX logic.

Anyway, this problem was comparatively straightforward to resolve. However after I thought-about a Month-over-Month calculation with the L4L performance, I noticed it wouldn’t be doable with out some DAX code. Presumably, I’ll dig into this in a future article.

However this case emphasizes the necessity to use the person’s perspective when designing and testing an answer.

It isn’t sufficient to make use of a technical perspective; the person’s perspective is rather more vital when evaluating the answer’s performance and outcomes.

For me, this was a really fascinating expertise and really helpful for my future work.

I hope that you simply discover my strategy fascinating. Keep tuned for my subsequent piece.

References

That is my earlier article on this subject: