Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):


Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI и Power Pivot сдвинуть даты вперед или назад для сравнения текущих показателей с прошлыми или будущими. А конкретно, разберем функции, отвечающие в DAX за этот процесс — DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR.

Разберем детально каждую из этих функций.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

 

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, получился столбец дат, сдвинутый в прошлое на два дня назад:

Результат работы формулы в Power BI на основе DAX функции DATEADD

 

То есть, каждой текущей дате из столбца [Дата] соответствует своя дата из прошлого на 2 дня назад (для текущей даты 5 января соответствует дата из прошлого 3 января).

Если мы в формуле укажем в качестве количества интервалов положительное число:

1 День Вперед = DATEADD ('Календарь'[Дата]; 1; DAY)

то сдвиг произойдет в будущее:

Функция DATEADD - сдвиг даты в будущее

 

То есть, каждой текущей дате из столбца [Дата] соответствует своя дата из будущего на 1 день вперед (для текущей даты 1 января соответствует дата из будущего 2 января).

 

DAX функция PARALLELPERIOD в Power BI и Power Pivot

PARALLELPERIOD () — возвращает таблицу из дат, смещенных во времени вперед или назад параллельно текущей дате в текущем контексте на заданное количество интервалов.

Синтаксис:

PARALLELPERIOD ([Дата]; Количество Интервалов; Интервал)

Где:

  • [Дата] — столбец из дат или выражений, возвращающих даты
  • Количество Интервалов — целое число, характеризующее количество интервалов, которое нужно добавить (вычесть) к дате в текущем контексте. Если указано положительное число — то интервалы добавляются, если указано отрицательное число — то интервалы вычитаются
  • Интервал — тип интервала: year (год), quarter (квартал), month (месяц)

! — Для правильной работы функции необходимо в качестве ее первого параметра [Дата] использовать столбец из таблицы непрерывных дат в Power BI, то есть, создавать отдельную связанную таблицу «Календарь» с непрерывным перечислением всех дат.

В общем и целом, функции DATEADD и PARALLELPERIOD практически одинаковы, за исключением того, что:

  1. в PARALLELPERIOD нет интервала day (день)
  2. 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:

Результат работы формул в Power BI Desktop на основе DAX функций DATEADD и PARALLELPERIOD

 

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

Обе функции DATEADD и PARALLELPERIOD пока работают абсолютно одинаково, так в чем же их различие?

А различие в том, что DATEADD — возвращает дату, которая была месяц назад. А PARALLELPERIOD весь период, который был месяц назад (в данном случае, период равен месяцу, так как в параметрах указан MONTH).

Давайте это подтвердим на нашем примере и в созданной матрице спустимся в иерархии дат до дней, тогда мы тут же заметим разницу в работе этих двух функций (для удобства демонстрации я создал две копии визуализаций: на первой показан январь по дням, на второй — февраль по дням):

Различие функций DATEADD и PARALLELPERIOD

 

То есть, теперь, когда мы спустились в матрице к отображению дней, то 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])
)

Работа формул в Power BI на основе DAX функций DATEADD и SAMEPERIODLASTYEAR

 

Как мы видим, формула на основе SAMEPERIODLASTYEAR, как и на основе DATEADD, вывела значение суммы продаж за январь предыдущего года. Соответственно, в тех случаях, когда нам нужно вычислить какие-то значения за прошлый год, то лучше просто воспользоваться SAMEPERIODLASTYEAR, так как она позволяет быстрее и легче написать DAX код в Power BI.

На этом, с разбором DAX функций DATEADD, PARALLELPERIOD и SAMEPERIODLASTYEAR, сдвигающих период дат в прошлое или будущее в Power BI или Power Pivot, в этой статье все.

Единственное, напоминаю, что для корректной работы этих функций, в качестве столбца дат требуется указывать совершенно отдельную в модели данных таблицу «Календарь» с неразрывными датами.

Успехов Вам, друзья!

 

Антон БудуевС уважением, Будуев Антон.
Проект «BI — это просто»

 

 

 

Присоединяйтесь к нашим социальным сетям

Именно в них оперативно и каждый день Вам будут доступны наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи 

по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel...)

Наша группа Вконтакте Мы в Инстаграме Наша группа в Фейсбук Наш YouTube канал
Наша группа VK
Подписаться на наш YouTube канал

 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

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

 

Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D


Вам это может быть интересно. Другие статьи нашего обучающего портала:


Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

* Заполняя форму отправки комментария своими персональными данными (имя, email, сайт и др.), Вы автоматически подтверждаете свое согласие на обработку своих персональных данных