Дополнение к решению Like-for-Like для магазинов: обработка PY

Эта статья является дополнением к предыдущей статье о разработке решения Like-for-Like (L4L) на базе Power Query:

Разработка решения Like-for-Like для магазинов в Power BI

Решение работало как ожидалось в большинстве случаев. Я показал его коллегам и некоторым клиентам.

Отзывы были положительными, но я получил несколько вопросов, и результаты моего решения не совпадали с ожиданиями тех, кто их задавал.

Проблема

Я обнаружил проблему при расчёте значения PY (предыдущий год).

Технически результаты верны, но с точки зрения пользователя они могут ввести в заблуждение.

Посмотрите на следующие два скриншота, на которых показаны два различных случая, включающие показатели Retail Sales и Retail Sales PY. Результаты этих двух случаев могут вызвать путаницу у аудитории.

Попробуйте обнаружить проблему перед тем, как продолжить чтение.

Рисунок 1 – Первый случай PY – Временно закрытый магазин (Refresh) (рисунок автора)

Это первый случай для магазина Турин, который был временно закрыт между марта и июля 2024 года.

Рисунок 2 - Второй случай PY – Смешанный случай: временное закрытие и постоянное закрытие магазина (рисунок автора)

А вот второй случай для магазина Рим, который был временно закрыт с августа по октябрь 2023 года и окончательно закрыт в августе 2024 года.

Для этого второго случая мы видим следующие результаты:

1. Значения для показателя Retail Sales PY для "Сопоставимых" магазинов, но с перерывом между августом и октябрем.
2. Значения для показателя Retail Sales для магазинов "Несопоставимые – Закрытие".
3. Значения для показателя Retail Sales PY для магазинов "Несопоставимые – Refresh".

С технической точки зрения эти результаты имеют полный смысл и являются корректными.

Показатели отображают правильные состояния L4L для текущего периода и предыдущего года.

Итак, в чём заключаются проблемы?

Для пользователя эти результаты очень запутанны и не соответствуют его ожиданиям.

Подумайте с точки зрения пользователя:

При просмотре результатов для определённых состояний L4L оба показателя должны присваивать результаты одному и тому же состоянию L4L, независимо от того, рассчитаны ли они для текущего периода или предыдущего года.

Это вносит новую сложность в решение.

Решение

Мне нужен второй столбец L4LKey для предыдущего года.

Для первого столбца L4LKey я сравниваю даты открытия и закрытия с месячными датами предыдущего года (см. подробности в первой статье).

Для второго столбца L4LKey_PY я должен сравнить эти даты с месячными датами того же года, что и даты открытия и закрытия.

Идея несколько контринтуитивна, но она дает необходимый результат.

Оставайтесь со мной, и вы увидите, как это работает.

Сначала я попытался решить эту задачу в Power Query, как я делал в исходном решении. Но это не сработало. Я расскажу о причине через минуту.

Затем я переключился на создание таблицы Bridge_L4L в SQL, но результаты снова оказались непригодными, так как я всегда получал дублирующиеся строки для магазина Рим, поскольку у меня было две строки для двух состояний L4L для этого магазина:

Рисунок 3 – Две строки для магазина Рим (ID 222) на два года 2023 и 2024 (рисунок автора)

У меня есть по одной строке для каждого временного закрытия в 2023 году и окончательного закрытия в 2024 году.

Поэтому объединение всегда возвращало две строки, так как ключ магазина был продублирован.

Таким образом, я решил перейти на процедурный подход.

Я прохожу через каждую строку в таблице, содержащей информацию об открытии и закрытии магазинов, и применяю состояния к таблице, которая имеет одну строку на магазин и месяц.

Я сделал это, используя временные таблицы в SQL и следующий SQL код:

-- 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;

Результатом процедуры является таблица, содержащая один столбец, отображающий состояния L4L на основе предыдущего года для каждого месяца (L4LKey), и один столбец, отображающий состояния L4L на основе того же года для каждого месяца (L4LKey_PY):

Рисунок 4 – Результат процедуры для таблицы Bridge_L4L с двумя столбцами L4LKey (рисунок автора)

Следующий шаг – импортировать результат этой процедуры в Power BI и добавить дополнительную связь между Bridge_4L и таблицей DIM_L4L для нового столбца L4LKey_PY:

Рисунок 5 – Модель данных с дополнительным столбцом L4LKey_PY и дополнительной связью с DIM_L4L (рисунок автора)

Это позволяет мне контролировать расчёт результата PY.

Retail Sales (PY) =
CALCULATE([Retail Sales]
            ,'Time Intelligence'[Time Measures] = "PY"
            ,USERELATIONSHIP('Bridge_L4L'[L4LKey_PY], 'DIM_L4L'[L4LKey])
            )

Теперь результаты соответствуют ожиданиям.

Вот первый случай:

Рисунок 6 – Результаты для магазина Рим на 2024 год. Теперь результаты согласованы (рисунок автора)

И вот результаты для второго случая:

Рисунок 7 – Согласованные результаты для магазина на 2025 год (рисунок автора)

Как вы можете видеть, значения PY присваиваются тому же состоянию L4L, что и результаты текущего года.

Теперь пользователь видит согласованные результаты, которые намного легче понять.

Заключение

Дополнительный вызов функции USERELATIONSHIP() может быть помещён в элемент расчёта и использован всеми показателями PY.