Урок №1 - Создаем вместе календарь и избавляемся от ошибок, которые придется исправлять в будущем.
Скачать готовый календарь со всеми функциями и срезами можно у нас на сайте - СКАЧАТЬ
Казалось-бы, что проще? Написал функцию CALENDARAUTO() и все готово, но как показывает практика - есть нюансы, и их много! Помимо нюансов еще есть и претензии к функциям CALENDARAUTO() и CALENDAR(StartDate, EndDate) - не использую их, есть более элегантное решение.
Сложности с календарем в Power BI приходят тогда, когда проект уже оброс большим количеством функций, мер, дашбордов и всего прочего, внесение корректировок в календарь влечет за собой большое количество исправлений или доработок, лучше подумать о них заранее и избавиться от таких заморочек.
Давайте по порядку:CALENDARAUTO() - отличная и простая функция, самое главное автоматическая, сама находит минимальное и максимальное значение даты в источниках данных, создает таблицу дат, которая каждый раз обновляется, не нужно каждый год что-то менять.
Претензия заключается, во первых, в том что не всегда корректно определяет минимальную дату, а во вторых, нет возможности при необходимости расширять диапазон. И это еще не все, дальше в статье расскажу вытекающие сложности.
CALENDAR(StartDate, EndDate) - более гибкая и функциональная формула, долгое время я именно ее использовал, наверное, как и многие другие пользователи Power BI. Больше всего меня напрягало в этой формуле то, что если нужно что-то изменить, слетают все иерархии даты во всех срезах, таблицах и дашбордах 🤦♂️🤬 Приходится заново везде все указывать те части иерархии, которые были до исправления. Некоторым дашбордам и это не помогает, даже после указания к примеру месяцев, приходится выставлять остальные данные, которые к календарю не имеют отношения.
На второй картинке видно что происходит со срезом, после изменения формулы календаря, если он взят из иерархии дат.
- Чем больше практики, тем больше убеждаюсь, что при создании каждого нового проекта, первым делом, нужно уделить должное внимание созданию календаря.
Есть просторе решение: создаем календарь в Power Query.
Что нам для этого нужно?
1. Идем в редактор Power Query:
2. Создать источник -> Пустой запрос
3. Сразу переименовываем его, два раза кликаем на новом запросе и называем как нам нравится, я напишу "Календарь Powe Query".
4. После этого идем в Расширенный редактор:
Вместо этого кода:
5. Вставляем:
let
#"Запрос для календаря" = List.Generate(
() => #date(Date.Year(DateTime.LocalNow())-1, 1, 1),
each _ <= #date(Date.Year(DateTime.LocalNow())+1, 12, 31),
each Date.AddDays(_, 1)
),
#"Преобразовано в таблицу" = Table.FromList(#"Запрос для календаря", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Измененный тип" = Table.TransformColumnTypes(#"Преобразовано в таблицу",{{"Column1", type date}}),
#"Переименованные столбцы" = Table.RenameColumns(#"Измененный тип",{{"Column1", "Дата"}})
in
#"Переименованные столбцы"
Давайте разберемся что мы указали:
Тут все просто, (Строка 3) мы взяли год из текущей даты, потом (-1), вычли из текущей даты один год, и получили минимальную дату нашего календаря, затем статично указали (1, 1) первый месяц и первый день месяца, таким образом у нас получился динамический календарь, который всегда берет первое января предыдущего года.
По аналогии с началом календаря в 4 строке мы указываем максимальную дату календаря, в нашем случае мы просто указываем +1, тем самым прибавляем к текущему году еще один и указываем 12 месяц и 31 число.
При желании, по аналогии с функцией CALENDAR(StartDate, EndDate) мы можем взять даты из источников данных, но лучше не зависеть ни от каких сторонних таблиц и мы всегда будем знать что календарь у нас не сломается, если "отвалится" источник.
Или вот еще один альтернативный вариант кода запроса календаря в Power Query:
let
// Получаем текущую дату
ТекущаяДата = DateTime.LocalNow(),
// Вычисляем дату начала (дней назад)
ДатаНачала = Date.AddDays(Date.From(ТекущаяДата), -10),
// Вычисляем дату окончания (дней вперед)
ДатаОкончания = Date.AddDays(Date.From(ТекущаяДата), +10),
// Создаем список дат
#"Запрос для календаря" = List.Generate(
() => ДатаНачала, // Начальная дата
each _ <= ДатаОкончания, // Условие завершения
each Date.AddDays(_, 1), // Шаг генерации
each _ // Возвращаем саму дату
),
// Преобразуем список в таблицу
#"Преобразовать в таблицу" = Table.FromList(#"Запрос для календаря", Splitter.SplitByNothing(), {"Дата"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Преобразовать в таблицу",{{"Дата", type date}})
in
#"Измененный тип"
В этом варианте мы указываем не количество елт а количество дней от текущей даты, иногда такой вариант удобен если у нас плавающий отчет в разрезе нескольких недель.
Собственно календарь у нас готов, закрываем Power Query и применяем изменения в Power BI.
Мы получили календарь на 3 года, как правило,
(Что было? Что есть? Что будет?)
это самый распространенный диапазон для отчетов.
Преимущества такого способа:
- контролируемый календарь, при изменении которого у нас ничего не сломается в отчете (в отличии от использования DAX).
- календарь не зависит от источников данных
- календарь автоматически смещается каждый год.
Скачать готовый календарь со всеми функциями и срезами можно у нас на сайте - СКАЧАТЬ
В следующем уроке мы добавим столбцы в таблице, которые необходимы для создания производственного календаря в Power BI.