27 подписчиков

Календари в Power BI. Сравнение возможностей.

30 июня 202130 июн 2021

1326

10 мин

Всем привет!
Сегодня поговорим о том, какие варианты создания календаря есть в Power BI. Чем они отличаются друг от друга, будем описывать по мере разбора каждого из них.

Зачем нужен календарь в Power BI?

Начнем с того, что определим зачем вообще создавать календарь.
Во-первых, календарь это один из самых важных справочник в модели данных.
Сразу приведу пример, который Вы сможете скачать отсюда.
У нас есть таблица с фактом продаж. В ней продажи отражены по дням с 06.01.2015 по 01.07.2016.
И у нас есть таблица с планом продаж. В ней план расписан по месяцам с января по декабрь 2016 года. Дата указана как первое число каждого месяца в формате день – месяц – год.
То есть, вроде бы можно связать две таблицы по дням. И что получим? Максимум сможем сравнивать факт продаж на 01.02.2016 (только на один день) с планом за месяц, который так же отнесен на 01.02.2016. Можно посчитать, конечно, сумму за месяц и отнести ее тоже на первое число или из даты сделать месяц и сравнивать по названию. Только это не сработает, когда в плане или факте появиться следующий год. Тогда нужно делать ключ месяц – год. И, в общем, совсем плохо станет, когда появится третья таблица с датами, которую надо будет с этими двумя связать.
Поэтому, нужно взять за правило, что в любой даже самой простой модели Power BI должен появляться первым делом справочник календарь. Так как он позволяет работать с временными рядами и делать соответствующие вычисления любой сложности за любой период. Например, сравнивать факт продаж за январь 2015 года, с планом на январь 2015 года. Не говоря уже о более сложных вычислениях, которые относят к разделу Time Intelligence в Power BI.

Как можно создать календарь в Power BI.

Перечислим варианты создания календаря в порядке сложности.

Вариант 1. Воспользоваться функцией DAX CALENDARAUTO.
Вариант 2. Воспользоваться функцией DAX CALENDAR.
Вариант 3. Создать календарь в Power Query.
Вариант 4. Найти в сети и скачать код календаря в DAX или Power Query.
Вариант 5. Упростить или усложнить найденный в сети код.

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

Есть ли отличия между вариантами создания календаря?

Мы с Вами сейчас на примере разберем особенности при создании справочника календарь по вариантам 1, 2 и 3. Сравнивать будем, создавая календари в файле Power BI. Скачать его можете отсюда.

Функция DAX: CALENDARAUTO

Давайте сначала познакомимся с этой функцией.
Официальная документация говорит о следующем:
“Возвращает таблицу с одним столбцом с именем Date, который содержит смежный набор дат. Диапазон дат вычисляется автоматически на основе данных в модели.
Диапазон дат вычисляется следующим образом.
- Самая ранняя дата в модели, которая не находится в вычисляемом столбце или вычисляемой таблице, принимается как MinDate.
- Самая поздняя дата в модели, которая не находится в вычисляемом столбце или вычисляемой таблице, принимается как MaxDate.
- Возвращаемый диапазон дат — это даты между началом финансового года, связанного с MinDate, и концом финансового года, связанного с MaxDate.”

Код функции очень простой. Нужно на вкладке «Моделирование» или в представлении «Данные» выбрать пункт «Создать таблицу», написать знак равно, функцию, открыть и закрыть скобки. Финансовый год система автоматически приравняет к календарному из региональных настроек.
То есть, как в нашем примере, код будет следующий:
2_Календарь = CALENDARAUTO()

Смотрим, какой результат мы получили. Минимальная дата: 01.01.2015. Максимальная дата: 31.12.2016. Все корректно, быстро и очень удобно. Теперь осталось только связать таблицы фактов и плана.

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

В чем подвох? Его особенно и нет. Но нужно учитывать следующее. Если Вам месяц как число, или усеченное название, например не «Январь», а «янв». Или Вам нужны номера недель, количество дней в месяце. То есть любые дополнительные расчеты за пределами иерархии дат.
То придется работать с вычисляемыми столбцами и функцией FORMAT и ее разделом «Настраиваемые форматы даты и времени». И с функциями DAX, которые относятся к дате и времени. А вот раздел DAX функций операции со временем в вычисляемых столбца не поддерживается. И вот тут уже, особенно с непривычки, времени можно потратить больше.
Но в целом, тоже все решаемо и даже интересно один раз попробовать и создать себе для будущих моделей шаблон календаря.

Функция DAX: CALENDAR

У этой функции одно ключевое отличие от предыдущей.
Для ее работы требуется указать дату начала календаря (StartDate) и дату окончания (EndDate).

Это можно сделать, задав даты вручную в формате год (2015), месяц (1), день (1). Но, это не работает, когда Вам нужно настроить автоматическое обновление дат календаря. Ведь сегодня 30 июня 2021 года, а завтра будет уже 1 июля. А Ваш календарь останется в прошлом, если Вы руками не обновите даты.
Поэтому на выручку приходит возможность чуть усложнить формулу и сослаться на поиск минимальной и максимальной дат календаря в нужных таблицах.
Давайте посмотрим на описание и синтаксис функции CALENDAR:
«Возвращает таблицу с одним столбцом с именем Date, который содержит смежный набор дат. Диапазон дат — от указанной даты начала до указанной конечной даты, включая эти две даты.
Для модели данных, которая включает фактические данные о продажах и прогнозы будущих продаж. Следующее выражение возвращает таблицу дат, охватывающую диапазон дат в этих двух таблицах.

= CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date])) »

А теперь стоп! Не будем повторять за рекомендациями официально документации!
По одной простой причине. Мы ведь хотим построить автоматически обновляемый календарь.
А у нас минимальная дата в таблице фактов – 06.01.2015. А последняя в таблице «План Продаж» 01.12.2016. То есть при таком синтаксисе мы получим календарь, который кончается 01.12.2016, если в декабре нам не обновят источник с плановыми показателями. Но лучше избежать зависимости.
Как быть. Есть два варианта. Можно указать дату окончания календаря лет так через 20 от сегодня. И забыть. Но будет очень неудобно постоянно убирать значения года, где нет фактических или плановых значений.
А можно дополнить код так, чтобы он брал начало года по таблице, где хранится минимальная дата, и конец года по таблице, где есть максимальная дата. Хотя в нашем примере это может одна и таже таблица. Но есть возможность того, что план у нас будет на несколько лет вперед, или квартал. То есть будет переход в следующий год.
Для этого нам потребуется:
- для определения даты как 01.01.2015 воспользуемся функцией DATE и функциями MIN, YEAR и MONTH.
DATE просит указать год, месяц и день, как число. Для этого нам потребуется MIN, чтобы выбрать минимальные значения из таблицы фактов по году и месяцу. А число мы укажем просто как 1 (единица).
С определением даты 31.12.2016 проще. Тут нам поможет функция EOMONTH, которая работает в вычисляемом столбце. Для нее мы еще возьмем функцию MAX, что она смогла присвоить дату конца месяца последнему (максимальному месяцу) в таблице «Продаж План», то есть сделать из 01.12.2016 нужную нам дату 31.12.2016.
Наш итоговый код будет таким:
1_Календарь =

CALENDAR(

// указываем дату началу календаря. Берем для функции DATE минимальное значение по году и месяцу.

DATE (YEAR (MIN ('Факт_Продаж'[Дата заказа])) , MONTH (MIN ('Факт_Продаж'[Дата заказа])) , 1) ,

// указываем дату окончания календаря. Берем для функции EOMONTH максимальную дату из таблицы.

EOMONTH(MAX('План_Продаж'[Месяц]),0)

)

Вот Вы и увидели отличия этих двух функций между собой.

Как создать календарь в Power Query и упростить себе жизнь.

Я всегда следую в работе с Power BI такому подходу. Если что-то можно сделать в Power Query с преобразованием таблиц, то стараюсь сделать там. Так как функционал Power Query сильно упрощает многие операции, которые делаются в DAX.
Аналогично и с календарем. Далее мы рассмотрим, как в несколько шагов сделать основу календаря. А потом по клику мышкой его настраивать, как Вам нравиться.
Для этого открываем Power Query и области запросов кликом правой кнопки мыши выбираем «Создать новый запрос» и далее «Пустой запрос».
Теперь нам нужно первым шагом указать источник с датами. Для этого просто в строке кода пишем следующее выражение: = Факт_Продаж[Дата заказа].
В этом шаге мы просто сказали PQ выбрать в качестве источника столбце с датами из таблицы «Факт продаж». Когда Вы начнете писать, то PQ будет Вам предлагать выбрать таблицы. Что также увеличивает скорость работы.
Далее начинаем создавать нашу структуру календаря. Для этого в области «Примененные шаги» по клику правой кнопки мыши на шаг «Источник» выбираем «Вставить шаг после».

Нам сейчас нужно указать дату начала календаря. Помня о том, что у нас в таблице фактов нет 1го января 2015 мы воспользуемся следующим кодом: = Date.StartOfYear( List.Min(Источник) )

Date.StartOfYear – выберет дату начала года. А List.Min укажет ей, на минимальную дату в столбце дат из таблицы «Факт продаж», то есть теперь нашем источнике. Шаг переименовываем в МинДата. И не забываем переименовывать все последующие шаги!

Добавляем новый шаг и указываем дату окончания календаря. Логика такая же, как и с датой начала. Код следующий: = Date.EndOfYear( List.Max(План_Продаж[Месяц]) )

Date.EndOfYear – выберет дату окончания года. А List.Max укажет ей, на максимальную дату в таблице, где эта дата есть. То есть в нашей таблице «План Продаж».

Следующим шагом нам надо посчитать количество дней между датами в календаре.
Для этого из максимальной даты вычтем минимальную: = МаксДата – МинДата

Но на выходе получим формат «Продолжительность», а нам нужно количество. Для этого в левом верхнем углу кликнем на «Продолжительность» и выберем значение «Дни». PQ создаст новый шаг, где будет уже указана цифра с количеством дней между датами. К итоговому значению нужно прибавить 1 (единицу), чтобы далее PQ смог сформировать полный последний год, а не обрезать его на 30.12.2016. Связана эта особенность с формулой вычитания между датами.
То есть код этого шага должен выглядеть так: = Duration.Days(#"Разница дней")+1 Далее нам нужно задать периодичность повторения, то есть, что бы дни в календаре шли один за другим. Поэтому в коде нового шага просто указываем данные в формате «Продолжительность» без знака равно: 1.00:00:00 (день, часы, минуты, секунды). Следующим шагом нам нужно сформировать из наших компонентов (шагов) получить список дат. Для этого специальная функция – List.Dates. Код шага будет таким: = List.Dates(МинДата,#"Количество дней в календаре",#"Продолжительность")

Где мы функции укажем дату начала (как МинДату), количество дней в календаре – то есть на какой дате остановится календарь (идентично EndDate) и с какой частотой дни должны идти друг за другом (как раз наша «Продолжительность»).
И последним шагом нам нужно преобразовать список в таблицу. Для этого в левом верхнем углу нажимаем на «В таблицу» и все готово!

А теперь логичный вопрос – мы все это сделали для чего?
Во-первых, Вы также можете сохранить код календаря себе на будущее – нужно только будет менять источник и таблицу для максимальной даты. Да и после трех-пяти повторений и понимания, как все работает – такая операция не займет больше нескольких минут.

А главный бонус в том, что может делать сам PQ для Вас и за Вас!
Тут нам помогут:
На ленте редактора выбираем «Добавление столбца» и «Столбец из примеров». Посмотрите, сколько всего предлагает Вам PQ для автоматизации процесса: 22 преднастроенных возможности!
И это еще не все! Сделайте дубликат столбца с датой. Сделайте правый клик и выберите «Преобразование». Снова дополнительные варианты трансформации значение даты в нужное нам.
А также возможность быстро объединять столбцы и работать с текстом. Например, как просто создать короткое название месяца с заглавной буквы? Дублируем столбец с названиями месяцев, выбираем на ленте меню «Преобразование», далее «Извлечь», далее «Длина» и указываем первые три символа! Готово!
На выходе можно получить вот такой полезный календарь за несколько кликов.