Following Shopping Preferences: Managing PY

The solution works as expected for the most part. I showed it to my peers and other customers.

The answer was good, but I have some questions, and the results of my solution were not what the questioner expected.

The issue

I got a problem while calculating the value of PY.

Technically, the results are good, but not from the user's point of view.

Take a look at the next two screenshots, which show two different cases involving the Retail Sales and Retail Sales PY steps. The results of these two cases can confuse the audience.

Try to identify the problem before reading on.

This is the first case of the Torino store, which is temporarily closed between March and July 2024.

And here is the second case of the Roma store, which was temporarily closed from August to October 2023 and permanently closed in August 2024.

We see these results in the second case:

1. PY averages for Store Sales at “Comparable” stores, but with disruptions between August and October.
2. Store Sales Rates measure “Unmatched – Closing” stores.
3. Average PY Sales Values ​​for “Non-Comparable – Refresh” stores.

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

The steps show the correct L4L states for the current season and the previous year.

So, what are the problems?

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

Think about it from the user's perspective:

If you are looking at the results of specific L4L regions, these two steps should give results for the same L4L state, regardless of whether it is calculated for the current period or the previous year.

This introduces a new complexity to the solution.

The solution

I need the second column of L4LKey from last year.

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

In the second column of L4LKey_PY, I have to compare these dates with the monthly date of the same year as the opening and closing date.

The idea is somewhat contradictory, but it brings the result I need.
Please stay with me, you will see how it goes

First, I tried to solve it in Power Query, as I did in the first solution. But it didn't work. I'll come to the reason in a minute.

Then I switched to creating the Bridge_L4L table in SQL, but the results were not reused, as I always got duplicate rows for the Roman store, since I have two rows for the two L4L regions of this store:

I have one line each for a temporary shutdown in 2023 and a definitive shutdown in 2024.

Therefore, a join always returns two rows, since the store key is a duplicate.

So, I decided to switch to a procedural method.

I enter each row in the table containing the opening and closing stores and insert the states into the table, which has one row for each 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 process is a table containing one column mapping the L4L regions based on the previous year for each month (L4LKey) and one column mapping the L4L regions based on the same year for each month (L4LKey_PY):

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

This allows me to control the calculation of 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 expected.

Here, the first case:

And here are the results of the second trial:

As you can see, the PY values ​​are assigned to the same L4L status as the current year's results.

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

The conclusion

Additional call for USERELATIONSHIP() the function can be placed in the Calculation object and used by all PY steps.

This makes it very easy to use without additional DAX thinking.

However, this challenge was easy to solve. But when I considered the calculation of the Moon over the month with L4L functionality, I realized that it would not be possible without some DAX code. Perhaps, 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 is not enough to use a technical perspective; The user's perspective is very important when evaluating the solution's performance and results.

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

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

References

Here is my previous article on this topic: