Создание и назначение таблицы дат в Power BI
Для чего необходима таблица дат?
При анализе данных в программе Power BI неизменно приходится сталкиваться с обработкой дат и времени. В частности, аналитику приходится отвечать на следующие вопросы;
- Каков полученный доход за тот же период прошлого года?
- Насколько выросла выручка по сравнению с прошлым аналогичным периодом
- Каков накопленный доход за год, квартал или месяц?
- Сколько новых клиентов сделали свой первый заказ каждый месяц?
- Какова стоимость имеющихся запасов продукции компании?
Power BI Desktop автоматически идентифицирует столбцы, содержащие даты, а затем создает иерархии дат и другие вспомогательные метаданные для вашей модели от вашего имени. Эти данные в дальнейшем можно использовать при создании функций отчета, таких как визуальные элементы, таблицы, быстрые меры, срезы и т. д. Power BI Desktop генерирует эти значения создавая скрытые таблицы от вашего имени, которые вы затем можете применить для своих отчетов и выражений.
Рисунок 1 Включение автоматической иерархии дат в Power BI
В то же время встроенные автоматические иерархии дат могут быть опасными, поэтому рекомендуется их отключать. В частности, они не будут работать правильно, если ваш расчётный год начинается нестандартно, например, в марте. В DAX имеются специальные функции анализа дат и времени, но они тоже в таких случаях будут давать сбой. И для таких вычислений необходимо будет применять функцию CALCULATE с фильтром по собственной таблице дат.
Далеко нередка ситуация, когда связи между таблицей дат и другими таблицами в модели основаны на столбцах с типом данных, отличным от Datetime. Например, если вы загружаете данные из источника, такого как хранилище данных, часто предоставляются определенные столбцы, представляющие даты. Эти столбцы (также называемые суррогатными ключами) часто хранятся как целое число и форматируются как yyyymmdd (например: 20201231 для представления 31 декабря 2020 года). Если у вас есть связи между таблицей дат и другими таблицами в вашей модели, которые используют такие столбцы, вам тмак же нужно будет задать собственную таблицу дат, чтобы использовать возможности временной аналитики.
Именно поэтому для надёжности необходимо наличие в модели специальной таблицы дат. Это не просто таблица с определенными периодами и не отдельная колонка, содержащая даты конкретных продаж в соответствующей таблице.
Таблица дат — это отдельная таблица, которая соответствует следующим требованиям:
- В ней должен присутствовать столбец с типом данных «дата» либо «дата/время », известный как столбец «дата» .
- Столбец с датами содержит только уникальные значения.
- В столбце дат нет пустых ячеек
- Даты не пропущены
- Столбец даты охватывает полные года. Год не обязательно является календарным годом (январь-декабрь) и может соответствовать финансовому году компании.
- Таблица дат должна быть помечена как таблица дат.
Для добавления в модель таблицы дат прежде всего необходимо ее создать. Для этого существует несколько способов. На данном занятии рассмотрим три варианта;
Создание таблицы дат в редакторе Power Query.
Создание таблицы дат формулами DAX/
Добавление в модель существующей таблицы дат из внешнего источника
Рассмотрим по очереди каждый из вариантов
Создание таблицы дат в Power Query
Перейдем в редактор Power Query. Для этого в Power BI Desktop на вкладке «Главная» нажмем кнопку «Преобразование данных». Если вы хотите создать таблицу дат с помощью Excel с дальнейшим подключением ее в модель Power BI, то на вкладке «Данные» в Excel выберите команду «Пустой запрос». Точное расположение данной кнопки зависит от версии. В частности, в Office 365 путь выглядит так:
«Данные» – «получить данные» – «Из других источников» – «пустой запрос»
Дальнейшие шаги опишем по редактору Power Query в Power BI Desktop. Шаги в Excel будут аналогичными, исключение первый шаг, так как пустой запрос уже будет создан. Итак…
Главная – создать источник – пустой запрос
Рисунок 2. Пустой запрос в Power BI’
Переименуем запрос, задав для него понятное имя, например календарь или даты
Рисунок 3. Переименование запроса
В строке формул указываем
= List.Dates(#date(2024,1,1),366,#duration(1,0,0,0))
Рисунок 4. Формула получения списка дат
Здесь
- List.Dates() – создать список дат
- #date(2024,1,1) – указываем начальную дату в формате год, месяц, день. В данном случае это 1 января 2024 года. Если у вас финансовый год начинается 1 июля и вам надо указать началом года именно этот день, то для того же 2024 года пишем #date(2024,7,1).
- 366 – количество дней в календаре. Указываете необходимое вам значение. Для надежности можно заранее в том же Excel определить разницу между нужными датами в календарных днях
- #duration(1,0,0,0) – приращение длительности с заданным шагом ( дни, часы, минуты, секунды). В данном случае шаг 1 день
Будет создан список необходимых дат
Открывается вкладка «Преобразование, нажимаем в ней кнопку «В таблицу»
Рисунок 5. Запуск преобразования списка дат в таблицу
Указываем отсутствие разделителя
Рисунок 6. Настройка создания таблицы
Нажимаем ОК, после чего даем команду «Закрыть и применить». В итоге в модели Power BI Desktop появляется таблица дат. Однако значения столбца с датами будут определяться как текст. Чтобы это исправить, нажимаем на столбец и на вкладке «средства работы о столбцами» меняем тип данных на «Дата и время»
Рисунок 7. Изменение типа данных столбца
Готово!
Рисунок 8 Готовая таблица дат из Power Query
Преимуществом способа наличие варианта создания таблицы дат в Excel с дальнейшим применением созданной таблицы как таблица дат для всех отчетов заданного периода
Создание таблица дат в DAX
В этом случае имеются два варианта
Вариант первый.
В подключенных данных нет информации по данным, или нам надо создать свой календарь для использования как таблицы дат. Тогда используем функцию DAX
CALENDAR
Переходим на вкладку «Моделирование» и нажимаем кнопку «Создать таблицу»
Рисунок 9. Создание таблицы
Предположим, нам нужна таблица дат с 1 июля 2023 года по 30 июня 2024 года.
Тогда указываем в строке формул для создания таблицы дат указываем:
= CALENDAR(“1/7/2023″,”30/6/2024”)
Порядок дней и месяцев в начальной и конечной дате зависит от локализации, поэтому для надежности используем дополнительно функцию DATE
= CALENDAR(DATE(2023,7,1),DATE(2024,6,30))
Вариант второй.
В модели присутствует добавленная таблица, содержащая столбец с датами. Тогда используем функцию CALENDARAUTO()
=CALENDARAUTO()
Если параметры не указаны, то начальной датой считает ся первое января для года минимальной даты в списке. Но при указании в качестве параметра номера месяца, то первой датой будет считаться первое число следующего по счету месяца, но с предыдущего года.
Разберем эту тонкость на практике. Предположим, что наша таблица содержи даты только за 2018 год и применим функцию CALENDARAUTO с пустым параметром. Тогда первой датой будет действительно 1 января 2018 года
Рисунок 10. Итоговая таблица CALENDARAUTO без параметра
Теперь укажем в качестве параметра июль. Изменим формулу следующим образом
=CALENDARAUTO(7)
В результате первой датой календаря стало … 1 августа 2017 года!
Рисунок 11. Итоговая таблица CALENDARAUTO с параметром
Преимущество способа – упрощенное создание таблиц дат по сравнению с предыдущим. С другой стороны – нельзя создать отдельную таблицу дат для использования применения в нескольких отчетах, так как даты других отчетов неизвестны. В этом случае, конечно, надо использовать функцию CALENDAR.
Добавление в модель существующей таблицы дат из внешнего источника
Если в используемом источнике данных уже присутствует таблица дат, то рекомендуется использование в качестве источника таблицы дат модели. эту таблицу. Такое происходит при подключении к хранилищу данных, поскольку в нем будет таблица измерений дат. Таким образом, текущая модель использует единый источник истины для времени в организации. Если разрабатывается модель DirectQuery, а ваш источник данных не включает таблицу дат, рекомендуется заранее добавить таблицу дат в источник данных. Добавленная таблица должна соответствовать всем требованиям моделирования таблицы дат. Затем вы можете использовать Power Query для подключения к таблице дат. Таким образом, ваши расчеты модели могут использовать возможности временной аналитики DAX.
Если в вашей модели уже есть таблица дат и вам нужна дополнительная таблица дат, вы можете легко клонировать существующую таблицу дат с помощью вычисляемой таблицы. Выражение вычисляемой таблицы — это имя существующей таблицы дат.
Преимущество способа – простота. Но для подключения необходимо, чтобы такие таблицы уже были. Поэтому все-таки первоначально все равно придется применить либо первый, либо второй способы.
Назначение таблицы дат
Итак, таблица дат, или – если простым языком – непрерывный календарь для нужного периода – создан. Но этого мало. Теперь нужно указать, что это именно таблица дат, а не просто их перечень. Кроме этого , надо заранее позаботиться о добавлении в таблицу дат столбцов, описывающих временную категорию даты, например, номер или название месяца, так как после назначения таблицы дат сделать это уже не получится. Для добавления таких категорий используются функции MONTH, FORMAT,IF и так далее. Более подробно все это рассматривается в следующих статьях по данной тематике.
Длчя назначение таблицы в качестве таблицы дат имеется два способа.
Способ 1.
На панели «поля» нажать правой кнопкой на таблице, которую планируется использовать в качестве таблицы дат, и выбрать пункт «Отметить как таблицу дат» > «Отметить как таблицу дат»
Рисунок 12. Отметка таблицы как таблицы дат в контекстном меню
Способ 2.
На панели поля выбрать нужную таблицу щелкнув ее левой кнопкой. После этого на вкладке «средства работы с таблицами нажать кнопку «Отметить как таблицу дат»
Рисунок 13. Назначение таблицы таблицей дат на вкладке
В обеих случаях появится окно, в котором надо включить переключатель «пометить как таблицу дат» и указать столбец выбранной таблицы, содержащий даты.
Рисунок 14. Назначение указанной таблицы таблицей дат
Когда задается пользовательская таблица дат, Power BI Desktop выполняет следующие проверки этого столбца и, чтобы гарантировать, что значения в этом столбце:
- Не повторяются.
- Не пустые.
- Представляют последовательность дат начиная с начала до конца выбранных годовых периодов.
- Для каждого значения задана одна и та же временная метка.
В дальнейшем чтобы изменить при необходимости выбор столбца даты в этой таблице выберите таблицу на панели Поля, затем щелкните правой кнопкой мыши таблицу и выберите «Отметить как таблицу дат» > «Параметры таблицы дат». В появившемся окне из раскрывающегося списка будет доступен для использования другой столбец дат
На этом наше знакомство с пользовательскими таблицами дат закончено. На следующем занятии поговорим о создании пользовательской иерархии в таблице дат и о применении созданной таблицы дат для установки связей в модели Power BI.
