PowerBI: 4 способа создать таблицу-календарь

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

Создать ее можно либо в PowerQuery, либо на уровне модели в DAX.

В Power Query. Внутри PowerQuery это можно сделать вручную через ссылку на столбец датами с таблицы-измерения, но способ получше — использовать одну из готовых функций. Например, KillerDateTable от Максима Уварова.

Плюсы
* Объемная таблица, которая покроет большинство «хотелок» по возможным разрезам дат
* Учет праздников
Минусы
* Ручное изменение даты старта и даты окончания по умолчанию
* Зависимость от внешних источников (функция тянет с data.gov.ru информацию о праздниках)

В DAX. Внутри модели данных через одну из техник ниже

Генерация серии дат

1) CALENDARAUTO()

С помощью функции CALENDARAUTO(), которая автоматически сканирует модель на предмет минимальной и максимальной даты. Очевидный недостаток такого подхода, что где-то может затесаться ошибочная дата (например, в результате ручного ввода указали неправильную дату из будущего) и тогда таблица-календарь будет слишком большой

2) CALENDAR( DATE(…), DATE(…) )
3) CALENDAR( MIN(…), MAX(…) )

С помощью функции CALENDAR(). У этой функции два аргумента — начальная дата и конечная дата в серии. Эти даты можно указать вручную либо через функцию DATE(), либо через функции MIN / MAX от столбца с датами.

+ некоторые формулы ниже для добавления столбцов с разрезами по датам от столбца [Date]

* MonthNo
= MONTH( [Date] )
* Month
= FORMAT( [Date], «MMM» )
* Year
= YEAR( [Date] )
* WeekNo
= WEEKNUM( [Date], 2 )
* Week
= «W» & FORMAT( WEEKNUM( [Date], 2 ), «00» )
* WeekDayType
= IF( WEEKDAY( [Date], 2 ) >5, «вых», «буд» )

Плюсы
* Гибкая настройка, просто привязаться к минимальной / максимальной дате
Минусы
* Нет информации о праздниках, ее нужно добавлять отдельно
* Придется настраивать каждый столбец с разрезом

Бонус:
Можно создать таблицу-календарь со всеми разрезами выше с помощью техники ниже:

Calendar =
ADDCOLUMNS (
    CALENDARAUTO (),
    «MonthNo», MONTH ( [Date] ),
    «Month», FORMAT ( [Date], «MMM» ),
    «WeekNo», WEEKNUM ( [Date], 2 ),
    «Week», «W» & FORMAT ( WEEKNUM ( [Date], 2 ), «00» ),
    «WeekDayType», IF ( WEEKDAY ( [Date], 2 ) > 5, «вых», «буд» )
)

PS Вместо CALENDAR() функции также можно использовать функцию GENERATESERIES()

Оставьте комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.