Биг дата. Часть 3.
Краткое содержание предыдущих серий.
В первой части мы узнали как ТОЧНО не надо работать с датами в PowerBI.
Во второй части посмотрели как можно делать, но лучше НЕ НАДО.
В третьей части разберём Best Practics работы с датами - использование справочника Календарь.
Если вы до сих пор ещё задаётесь вопросом: зачем создавать отдельный справочник дат? - то я вам советую всё таки прочитать первые две части "Биг дата", прежде, чем двигаться дальше. А здесь отмечу:
Чтобы создать справочник Календарь, существует несколько способов:
- Загрузить уже готовый справочник из источника - как правило это характерно для баз данных, в которых уже есть такой справочник со всеми нужными полями
- Создать в редакторе PowerQuery - как это сделать можно легко нагуглить в интернете
- Использовать функции DAX в модели данных
Я предпочитаю варианты 1 и 3. Первый за стандартизацию и единый источник, а третий за гибкость в управлении и простоту создания.
Так как не у всех есть база данных, то в своём примере я буду использовать Календарь созданный с помощью DAX.
При создании справочника Календарь необходимо учитывать следующие правила:
- Столбец дат должен содержать уникальные значения
- Столбец дат не должен содержать ПУСТЫЕ ЗНАЧЕНИЯ - если в справочнике в столбце есть пустая строка, то она воспринимается как множественное значение, что не соответствует пункту 1
- В столбце дат не должно быть отсутствующих дат - то, есть нельзя допускать пропуска дат. Например: 01/01/2021, 03/01/2021 - отсутствует 02/01/2021, что не допустимо.
- Столбец дат должен иметь гранулярность по дням (без долей дня) - если столбец имеет тип Дата-Время, то время должно быть равно 00:00:00
- Столбец дат должен иметь тип Дата или Дата-Время
- Календарь должен быть связан с фактами по столбцам типа Дата или Дата-Время, иначе Календарь должен быть помечен как таблица дат
Для создания справочника Календарь лучше всего подходят функции CALENDAR и CALENDARAUTO.
Функция CALENDARAUTO возвращает таблицу с одним столбцом Date, который содержит диапазон дат вычисленных автоматически на основе данных в модели. То есть эта функция просматривает ВСЮ модель данных и на основе ВСЕХ столбцов с типом Дата и Дата-Время строит таблицу с 1 января минимального года в модели до 31 декабря максимального года в модели. Поэтому следует быть осторожными, применяя данную функцию.
CALENDARAUTO указывается без аргументов, если только не требуется изменить начало и конец года, тогда в качестве аргумента можно использовать любое выражение DAX, возвращающее целое число от 1 до 12, указывающее на ПОСЛЕДНИЙ месяц финансового года.
Чаще я использую функцию CALENDAR, которая также возвращает таблицу с одним столбцом Date, но границы которой я контролирую самостоятельно.
Я создаю таблицу сразу с нужными мне столбцами, так как такой код проще перенести затем в другой проект, чем переносить затем каждый вычисляемый столбец по отдельности.
После создания справочника необходимо настроить связи в модели. В нашем примере таблица фактов содержит два столбца с датами, поэтому одна из связей создаётся неактивной:
Обратите внимание: Календарь по связи находится на стороне 1 и фильтрует таблицу Orders (направление стрелки).
Создадим меру для расчёта значения предыдущего месяца:
В этот раз в качестве аргумента мы спокойно может сослаться на столбец Date таблицы справочника.
Проверяем работу меры в визуализации:
Изменим визуализацию на матрицу и вместо столбца Year-Month возьмём столбцы Year Number и Month Name
Мера всё та же - мы в ней ничего не меняем, но она по прежнему прекрасно считает. А что на счёт объёма отгрузок по месяцам? Там у нас используется другой столбец, а связь со справочником, как вы помните, не активна. Чтобы её активировать воспользуемся функцией USERELATIONSHIP
Сразу же создадим меру для расчёта отгрузок за предыдущий месяц
Меры прекрасно работают, при этом их написание лаконично и понятно.
А как меры будут вести себя, если установить фильтр по столбцу Country?
Фильтр по срезу Country прекрасно работает. Ни один месяц не потерялся.
И в конце ещё немного о визуализации.
Как сохранить отчёт так, чтобы он всегда открывался с фильтром на текущую дату?
У себя в телеграмм канале PowerBI. Extract solutions я приводил множество решений этого вопроса. Сам я чаще всего использую способ разметки дат в календаре.
В справочник Календарь добавлен столбец CurrentDay, который имеет три значения: -1 - прошлые даты, 0 - текущая дата, 1 - будущие даты.
Но нули и единички не очень красиво смотрятся, поэтому воспользуемся пользовательским форматированием столбца: "Будущие";"Прошлые";"Текущая" - именно в таком порядке, так как сначала форматирование задаётся для положительных значений, затем для отрицательных, а потом для нуля.
Текст можно было бы сделать и в столбце, но тогда пришлось бы всё равно создать числовой столбец, чтобы задать верную сортировку. А про пользовательские форматы можно прочитать в другой моей статье
Добавим столбец Date и выберем "Текущая"
Теперь при обновлении отчета дата будет меняться, так как мы сохранили фильтр на значении столбца CurrentDay - "Текущая", а не по столбцу Date, выбрав конкретную дату. При этом пользователю доступна возможность выбора любой даты.
Но этот срез можно ещё улучшить. Если мы развернём значение "Прошлые" в столбце CurrentDay до Date, то даты будут идти от начала календаря, и, чтобы выбрать ближайшую дату к текущей, придётся проматывать ползунок очень долго.
Было бы удобно, чтобы ближайшие даты располагались вверху списка. Для этого в справочнике Календарь я создал столбец DoubleDate и столбец DoubleDateSort
Дублирующий столбец дат нужен только для этого среза, чтобы основной столбец можно было использовать в визуализациях, не нарушив порядок дат.
Задаём сортировку DoubleDate по столбцу DoubleDateSort и заменяем столбец Date на DoubleDate в срезе:
Вот так, имея отдельный справочник Календарь можно создавать простые и понятные меры с функциями Time Intelligence, управлять визуализациями создавая дополнительные измерения, а также использовать нестандартные периоды.
Если вы хотите создавать действительно хорошие отчёты, то не пожалейте времени на создание хорошего Календаря!
По ссылке файл с моделью данных и моим календарём, созданным с помощью DAX.