Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI и Power Pivot сдвинуть даты вперед или назад для сравнения текущих показателей с прошлыми или будущими. А конкретно, разберем функции, отвечающие в DAX за этот процесс — DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR.
Разберем детально каждую из этих функций.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
DAX функция DATEADD в Power BI и Power Pivot
DATEADD () — создает таблицу со столбцом из дат, сдвинутых назад (в прошлое) или вперед (в будущее) на заданное количество интервалов от даты текущего контекста.
Синтаксис:
DATEADD ([Дата]; Количество Интервалов; Интервал)
Где:
- [Дата] — столбец из дат или выражений, возвращающих даты
- Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
- Интервал — тип интервала: year (год), quarter (квартал), month (месяц), day (день)
! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.
Разберем работу DAX функции DATEADD на примере нескольких формул.
В Power BI Desktop имеется таблица с датами «Календарь»:
Для создания формул сравнения текущих показателей с прошлыми, необходимо внутри этих формул, относительно текущей даты, как-то получить соответствующую дату в прошлом. Для этого, воспользуемся рассматриваемой функцией DATEADD.
И для примера того, как она сдвигает даты, создадим в таблице «Календарь» вычисляемый столбец на основе следующей формулы:
2 Дня Назад = DATEADD ('Календарь'[Дата]; -2; DAY)
В первом параметре этой формулы мы указали ссылку на столбец даты в календаре (календарь должен быть связан с той таблицей фактов, по которой Вы рассчитываете показатели).
Во втором параметре — количество интервалов, на которое нужно сдвинуть даты, причем значение отрицательное, то есть, сдвиг будет в прошлое.
В третьем — тип самого интервала (день).
Как результат выполнения этой формулы на основе DATEADD, получился столбец дат, сдвинутый в прошлое на два дня назад:
То есть, каждой текущей дате из столбца [Дата] соответствует своя дата из прошлого на 2 дня назад (для текущей даты 5 января соответствует дата из прошлого 3 января).
Если мы в формуле укажем в качестве количества интервалов положительное число:
1 День Вперед = DATEADD ('Календарь'[Дата]; 1; DAY)
то сдвиг произойдет в будущее:
То есть, каждой текущей дате из столбца [Дата] соответствует своя дата из будущего на 1 день вперед (для текущей даты 1 января соответствует дата из будущего 2 января).
DAX функция PARALLELPERIOD в Power BI и Power Pivot
PARALLELPERIOD () — возвращает таблицу из дат, смещенных во времени вперед или назад параллельно текущей дате в текущем контексте на заданное количество интервалов.
Синтаксис:
PARALLELPERIOD ([Дата]; Количество Интервалов; Интервал)
Где:
- [Дата] — столбец из дат или выражений, возвращающих даты
- Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
- Интервал — тип интервала: year (год), quarter (квартал), month (месяц)
! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.
В общем и целом, функции DATEADD и PARALLELPERIOD практически одинаковы, за исключением того, что:
- в PARALLELPERIOD нет интервала day (день)
- PARALLELPERIOD возвращает параллельный период полностью (например, от начала до конца месяца), тогда как DATEADD возвращает конкретно только тот интервал, который задан в изначальном столбце дат.
Например, изначальный набор дат будет отфильтрован датами от 16 до 25 октября. И если мы обеими функциями попытаемся возвратить набор дат на 1 месяц назад, то DATEADD возвратит даты только с 16 до 25 сентября, а PARALLELPERIOD — уже возвратит даты всего предыдущего месяца от начала и до конца, то есть, с 1 по 30 сентября.
Давайте разберем разницу работы DATEADD и PARALLELPERIOD на конкретном практическом примере.
В Power BI Desktop имеется исходная таблица по продажам за 2018 год (с 16.01.2018 по 31.12.2018):
Задача состоит в следующем — в рамках сводной таблицы, напротив текущего месяца, получить сумму продаж за предыдущий месяц. Для этого подойдут обе рассматриваемые выше функции.
Для корректной работы DATEADD и PARALLELPERIOD мы не можем ссылаться на столбец дат, который находится в таблице продаж. Для них нужна совершенно отдельная таблица неразрывных дат «Календарь». В разборе синтаксисов функций я об этом писал (напоминаю, как создавать таблицы неразрывных дат (календари) разбирается в этой статье).
Итак, в модели данных я создал отдельный «Календарь» и связал его со столбцом [Дата Продаж] в таблице «Продажи»:
Теперь можно приступать к созданию формул:
ПрМесDA = CALCULATE ( SUM ('Продажи'[СуммаПродаж]); DATEADD ('Календарь'[Date]; -1; MONTH) ) ПрМесPP = CALCULATE ( SUM ('Продажи'[СуммаПродаж]); PARALLELPERIOD ('Календарь'[Date]; -1; MONTH) )
Обе формулы, которые мы записали выше, рассчитывают сумму прибыли за предыдущий месяц:
- за суммирование отвечает функция SUM, которая суммирует все значения в столбце [СуммаПродаж]
- за перемещение периода отвечают функции DATEADD и PARALLELPERIOD, которые перемещают даты из календаря на 1 месяц назад, причем, напомню, «Календарь» связан с таблицей «Продажи» по столбцам дат
- и, за само вычисление суммы, под условием перемещения дат на месяц назад, отвечает DAX функция CALCULATE, внутрь которой помещены все функции, участвующие в работе
Посмотрим как поведут себя эти формулы в Power BI Desktop:
Как мы видим, формулы отработали как нужно и для текущего месяца (например, февраль) дают сумму продаж за предыдущий месяц (январь).
Обе функции DATEADD и PARALLELPERIOD пока работают абсолютно одинаково, так в чем же их различие?
А различие в том, что DATEADD — возвращает дату, которая была месяц назад. А PARALLELPERIOD весь период, который был месяц назад (в данном случае, период равен месяцу, так как в параметрах указан MONTH).
Давайте это подтвердим на нашем примере и в созданной матрице спустимся в иерархии дат до дней, тогда мы тут же заметим разницу в работе этих двух функций (для удобства демонстрации я создал две копии визуализаций: на первой показан январь по дням, на второй — февраль по дням):
То есть, теперь, когда мы спустились в матрице к отображению дней, то DATEADD возвращает для каждого текущего дня сумму продаж именно по этому же дню, но из прошлого месяца. А PARALLELPERIOD возвращает для каждого текущего дня сумму продаж уже не по этому же дню из прошлого месяца, а именно сумму продаж за весь прошлый период (в данном случае, за месяц).
DAX функция SAMEPERIODLASTYEAR в Power BI и Power Pivot
SAMEPERIODLASTYEAR () — возвращает таблицу из дат, смещенных на 1 год назад относительно дат текущего контекста.
Синтаксис:
SAMEPERIODLASTYEAR ([Дата])
На самом деле, SAMEPERIODLASTYEAR это упрощенный вариант DAX функции DATEADD, а именно, упрощенный ее вариант с конкретными настройками параметров:
SAMEPERIODLASTYEAR ([Дата]) = DATEADD ([Дата]; -1; year)
И нужна эта функция только для того, чтобы упростить написание кода на языке DAX в Power BI.
Посмотрим на практике как работают формулы на основе этих двух функций:
ПрГодDA = CALCULATE ( SUM ('Продажи'[СуммаПродаж]); DATEADD ('Календарь'[Date]; -1; YEAR) ) ПрМесSPLY = CALCULATE ( SUM ('Продажи'[СуммаПродаж]); SAMEPERIODLASTYEAR ('Календарь'[Date]) )
Как мы видим, формула на основе SAMEPERIODLASTYEAR, как и на основе DATEADD, вывела значение суммы продаж за январь предыдущего года. Соответственно, в тех случаях, когда нам нужно вычислить какие-то значения за прошлый год, то лучше просто воспользоваться SAMEPERIODLASTYEAR, так как она позволяет быстрее и легче написать DAX код в Power BI.
На этом, с разбором DAX функций DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR, сдвигающих период дат в прошлое или будущее в Power BI или Power Pivot, в этой статье все.
Единственное, напоминаю, что для корректной работы этих функций, в качестве столбца дат требуется указывать совершенно отдельную в модели данных таблицу «Календарь» с неразрывными датами.
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
Спасибо за статью. Единственное, формула DATEADD ([Дата]; -1; year)
сработала через » , » а не через » ; «