Глава 25. Пользовательский календарь в Power Pivot

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава        Содержание    Следующая глава

Мы очень любим создавать пользовательские календари в DAX. Тем не менее, нам потребовалось некоторое время, чтобы открыть волшебную формулу. Сейчас мы поделимся с вами нашим секретом.

Существует много типов пользовательских календарей: студенческий, сельскохозяйственный, спортивный и др.

Рис. 25.1. Пример пользовательского календаря 4/5/4, используемого в розничной торговле[1]

Скачать заметку в формате Word или pdf, примеры в формате Excel

Таблица периодов на примере 4/5/4

Допустим, наша компания работает по календарю 4/5/4, который очень распространен в рознице. 4/5/4 означает количество недель в каждом периоде, когда период составляет примерно месяц. Таким образом каждый квартал состоит из 13 недель. Ниже пример таблицы периодов, импортированной в Power Pivot (рис. 25.2). Обратите внимание на повторяющийся шаблон 28/35/28 дней, или 4/5/4 недель.

Рис. 25.2. Таблица периодов – 42 строки, охватывающие период с 01.07.2001 по 25.12.2004

Стандартные функции, такие как DATESYTD(), DATEADD() и SAMEPERIODLASTYEAR() по умолчанию используют стандартный календарь, и здесь будут работать некорректно. Чтобы обеспечить сравнение «год к году», нужно написать пользовательские версии этих функций, на основе FILTER(), ALL(), DATESBETWEEN() и т.п.

Добавим столбец PeriodID в существующую таблицу Calendar (рис. 25.3). Поле PeriodID позволит нам подключить таблицу периодов к таблице Calendar, которая в свою очередь подключена к таблице Sales (рис. 25.4).

Рис. 25.3. Добавляем столбец PeriodID в таблицу Calendar

Рис. 25.4. Связи в модели данных Power Pivot

В качестве альтернативы можно было бы построить пользовательский календарь (Periods) для каждого дня и подключить ее непосредственно к таблице данных (Sales). Любой подход приемлем, и формулы, которые мы напишем, поддержат и этот вариант.

Простая мера Продажи за период

Оказывается, что существующая мера…

[Total Sales] = SUM(Sales[SalesAmt])

… поддерживает продажи за период:

Рис. 25.5. Отображение данных о продажах в соответствии с пользовательской таблицей периодов

Это работает, так как «фильтры» текут вниз через связи через любое количество уровней. Фильтры по периодам будут передаваться в таблицу Calendar, а затем в таблицу Sales (см. связи на рис. 25.4).

Чтобы продемонстрировать, как фильтры перетекают из таблицы в таблицу, определим еще две меры:

[PeriodStartDate] = FIRSTDATE(Periods[Start])

[PeriodEndDate] = LASTDATE(Periods[End])

Видно, что объем продаж [Total Sales] за Р2 суммируется с 26.01 по 22.02.2003.

Рис. 25.6. Меры [PeriodStartDate] и [PeriodEndDate] наглядно показывают даты периодов

Продажи в день

Определим меру, показывающую общее количество дней в выбранном диапазоне:

Days in Period = SUM(Periods[Days])

Поместим ее в сводную таблицу, и добавим для наглядности условное форматирование (рис. 25.7). Естественно, продажи в «длинные» периоды больше. Чтобы сравнение периодов было релевантным, добавим меру, характеризующую продажи за период в расчете на один день (рис. 25.8):

[Sales per Day in Period] = DIVIDE([Total Sales]; [Days in Period])

Рис. 25.7. Обратите внимание, что каждый третий период больше (из-за структуры 4/5/4)

Рис. 25.8. Мера [Sales per Day in Period] позволяет сравнить «яблоки с яблоками»

Продажи в день в предыдущем периоде

До сих пор всё было знакомо. А теперь рассмотрим новую концепцию. Сначала добавим столбец YearPeriod в таблицу периодов, чтобы создать уникальную метку для каждого периода независимо от того, к какому году он принадлежит:

Рис. 25.9. Уникальная метка для периодов

В окне Power Pivot выберите таблицу Periods, выделите столбец YearPeriod, и кликните кнопку Сортировка по столбцам. Настройте сортировку столбца YearPeriod по столбцу Start, чтобы метка YearPeriod сортировалась «правильно» (по возрастанию дат), а не в алфавитном порядке по умолчанию (что было бы неприятно).

Рис. 25.10. Настройте Сортировку по столбцам, чтобы метка YearPeriod «правильно» сортировалась в сводных таблицах

Выведем новую метку YearPeriod в область строк сводной таблицы:

Рис. 25.11. PeriodYear в области строк и [Sales per Day in Period] в области значений

Наша цель – получить следующую картину:

Рис. 25.12. Мера, которая возвращает продажи за предыдущий период

Величайшая формула в мире

Давайте продолжим в стиле «работа в обратном направлении»: просто представим формулу, а затем объясним ее:

Мы полушутя-полусерьезно называем этот паттерн величайшей формулой в мире (английская аббревиатура GFITW). Этот шаблон выходит за рамки рассматриваемой темы, и довольно широко применим. Не переживайте, если вы не поймете с первого раза, как он работает.

Роль фильтра в формуле

GFITW можно дать и другое имя: Очистить все фильтры, а затем наложить новый фильтр. Этот новый контекст фильтра можно задавать, используя арифметику, которая перемещается по периодам в пользовательской таблице Periods. С таким подходом нетрудно разобрать GFITW на его составные части:

FILTER(ALL(<пользовательская таблица периодов>) – очистить все фильтры в таблице Periods

FILTER(ALL(… <условие выбора строки>) – повторный фильтр; здесь вы создаете новый контекст фильтра с нуля, используя ту логику, которая требуется.

Рассмотрим расчет меры [Prior Period Sales per Day] в ячейке D4, выделенной на рис. 25.12. Мы хотим пройти через таблицу Periods строка за строкой и выбрать только строки, которые нам нужны для расчета предыдущего периода. Поскольку контекст сводной таблицы в ячейке D4 передает в меру значение 2001-Р8, для начала нам надо избавиться от него. Этой цели служит первый аргумент функции FILTER – <таблица>: FILTER(ALL(Periods); …). Теперь у нас есть вся таблица Periods целиком, и мы накладываем на нее новый контекст фильтра во втором аргументе функции FILTER – <условие>:

FILTER (…; Periods[PeriodID] = MAX(Periods[PeriodID]) — 1)

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

Сначала это кажется странным: как можно отобрать строку, для которой PeriodID равен самому себе минус один? Ответ заключается в другой важной детали функции FILTER(). Table[Column] использует контекст строки, сгенерированный функцией FILTER (см. подробнее о контексте строки).

Periods[PeriodID] – это «голая» ссылка на столбец, которую мы узнали в начале книги нельзя использовать внутри меры (потому что понятия текущей строки нет внутри меры). Тем не менее функция FILTER, будучи итератором, проходит по таблице строка за строкой, и создает контекст строки. Таким образом, Periods[PeriodID] относится к определенной строке таблицы Periods, через которую проходит FILTER. И помните, что он проходит через всю таблицу Periods, так как мы использовали ALL(Periods) в качестве первого аргумента функции FILTER.

Так что же мы сравниваем в условии <Periods[PeriodID] = MAX(Periods[PeriodID]) – 1>?

MAX() – это функция агрегации и работает с контекстом фильтра. А какой контекст фильтра? Есть только один, исходный контекст фильтра, транслируемый из сводной таблицы.  Для ячейки, которую мы используем в качестве примера (D4 на рис. 25.12), контекст фильтра Period[YearPeriod] = "2001-P8". Это означает, что MAX(Periods[PeriodID]) вернет PeriodID = 2. Следовательно условие <Periods[PeriodID] = MAX(Periods[PeriodID]) – 1> вернет значение PeriodID = 1.

Рис. 25.13. Анатомия функции FILTER, используемой для GFITW: 1) исходный контекст фильтра переопределяется функцией ALL(); 2) применяется контекст строки, сгенерированный функцией FILTER (итератором); 3) применяется исходный контекст фильтра (из сводной таблицы)

Поскольку использование условий всегда сводится к какой-то математике, в таблице обязательно нужен столбец PeriodID, который:

• содержит уникальный номер для каждой строки
• увеличивается на единицу от строки к строке
• имеет последовательные номера для периодов, которые являются последовательными во времени

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

Создадим еще несколько мер. Но прежде переименуем меру продаж (чтобы название лучше отражало суть):

Sales in Period = [Total Sales]

Продажи за предыдущий период. Скопируйте шаблон GFITW и замените в нем <базовую меру>:

Продажи год к году (year over year, YOY):

Рис. 25.14. Мера [YOY Period Sales] показывает продажи 12 периодов назад

Продажи с начала года (year to date, YTD). Для начала добавим колонку в таблицу Periods:

Рис. 25.15. Вычисляемый столбец FirstPeriodInYear; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Мы объясним эту формулу в следующей главе. Сейчас же лишь упомянем, если ваши данные поступают из базы данных, лучше реализовать такой столбец именно там. Или, как вариант создайте пользовательский столбец при импорте данных с помощью Power Query.

Рис. 25.16. Продажи с начала года

Посмотрите, как работает формула меры для выделенной ячейки (рис. 25.17). Контекст входящего фильтра = "2001-P11". Для него MAX([PeriodID]) = 5 и MAX([FirstPeriodInYear]) = 1. Функция FILTER берет всю таблицу Periods, и выбирает из нее строки с PeriodID от 1 до 5 включительно.

Рис. 25.17. Работа фильтров и арифметики GFITW

Меры, написанные выше для пользовательских календарей, могут давать неожиданные результаты. В частности, когда контекст фильтра сводной подразумевает не один период, а итоги или подытоги. Это связано с тем, что логику, которую мы использовали, очень специфична и работает только на уровне периодов. Потребуются некоторые исправления, чтобы меры работали на итогах/промежуточных итогах.

Отключение итогов

Во-первых, можно просто не показывать значения в итогах. Ниже мера [Prior Period Sales] отображает неверное значение для итогов года:

Рис. 25.18. Мера Продажи за предыдущий период неверно отрабатывает итоги

Если мы хотим отключить итоги, достаточно ввести проверку в нашу меру:

Рис. 25.19. Проверка с помощью функции HASONEVALUE() подавляет меру для итогов

Исправление подытогов

Мера [YoY Period Sales] также работает некорректно в ячейках итогов / промежуточных итогов (часть формулы, влияющая на это, выделена полужирным):

Рис. 25.20. Можем ли мы заставить [YOY Period Sales] работать на уровне итогов?

Сначала давайте попробуем определить, почему мера не работает для выделенной ячейки промежуточного итога.

Для ячейки К7 входящий контекст фильтра Periods[Year] = 2003. Однако, внутри функции CALCULATE контекст фильтра изменяется. Сначала FILTER(ALL(Periods); …) снимает все фильтры с таблицы Periods. Далее функция-итератор FILTER пойдет по таблице Periods строка за строкой, и будет отбирать строки, удовлетворяющие условию <Periods[PeriodID] = MAX(Periods[PeriodID]) – 12>. MAX() является функцией агрегации, а контекст фильтра (Periods[Year] = 2003) никуда не делся. Так чему равно максимальное значение Periods[PeriodID] за 2003 год?

Рис. 25.21. MAX() работает с текущим контекстом фильтра из сводной таблицы

Правильно MAX(Periods[PeriodID]) для 2003 г. = 30. А MAX() – 12 = 18, т.е. YearPeriod = "2002-P12". И это то, что отражает наша сводная таблица. Значение в ячейке К7 на рис. 25.20 соответствует продажам за 12-й период предыдущего 2002 г.

Рис. 25.22. Золотые правила мер DAX помогают понять работу формулы

Теперь мы понимаем, почему наша мера не работает. Чтобы исправить это, хорошо бы взять весь текущий период из контекста фильтра сводной и подсчитать продажи за аналогичный период прошлого года. Лучший способ сделать это – вычесть 12 из начала текущего периода и 12 из его конца. Поэтому мы вычитаем 12 из MIN() и 12 из MAX() периода и выбираем строки, попадающие в этот диапазон. Обратите внимание, что в случае единичного периода, начало и конец соответствуют одному и тому же идентификатору периода, поэтому для одного периода ничего не изменится.

Этот сдвиг назад на 12 периодов работает для всех подытогов:

Рис. 25.23. Мера [YoY Period Sales Totals Fixed] корректно отражает все подытоги

Мы можем поправить и базовую меру [Sales per Day in Period], чтобы и она верно отражала подытоги:

Обобщение на случай любых подытогов

Когда мы первый раз ввели меру [Prior Period Sales], мы просто подавляли значение для более чем одного PeriodID. Давайте научим нашу меру показывать подытоги за «соответствующий» период. Это сложнее, чем было в примерах выше, поскольку, если текущая ячейка относится к кварталу, нам нужно сдвинуться назад на 3 периода, а если текущая ячейка относится к году – то на 12. Вот наша мера:

Таким образом, вместо вычитания фиксированного числа (3 или 12), мы вычитаем COUNTROWS(Periods), которое является числом выбранных в данный момент периодов; другими словами, это «размер» текущего выбора периодов времени.

Примечание. В Excel-файле с примерами сохранены все версии мер, представленных в заметке. Это сделано, чтобы показать, как по-разному они работают. Для ваших моделей, естественно, сохраняйте только финальную версию, потому что именно она работает корректно.

Рис. 25.24. Подытоги за предыдущий период теперь релевантны и для квартала, и для года

Формулы процентного роста

Теперь вы можете сделать стандартный трюк «новый минус старый, разделенный на старый», чтобы получить процент роста. Вы можете сделать это с любой мерой. Мы используем [Sales per Day] в качестве примера.

Лучшая практика. Мы уже говорили об этом раньше, и мы еще раз скажем, повторно используйте ранее созданные меры, везде, где сможете, и определяйте «производные» меры, а не пишите повторно ту же формулу в новой мере

Sales per Day in Period YoY Growth =
[Sales per Day in Period] — [YOY Period Sales per Day]

Sales per Day in Period YoY Growth % =
DIVIDE([Sales per Day in Period YoY Growth];
[YOY Period Sales per Day])

Рис. 24.25. Мера процентного роста для пользовательского календаря

[1] Календарь 4-5-4 – используется в розничной торговле, и обеспечивает сопоставимость продаж между годами. В календаре год разделен на месяцы на основе формата 4 недели – 5 недель – 4 недели. Расположение календаря выравнивает праздники и обеспечивает одинаковое количество суббот и воскресений в сопоставимые месяцы. Следовательно, подобные дни сравниваются с подобными днями для целей отчетности о продажах. Подробнее см. NRF.com