Создание календаря при помощи DAX

Рвав-рвав, собака Смайл продолжает эфир!

Мы с хозяином собрались с силами, и решили написать свою 1-ю статью, посвященную созданию календаря. Несмотря на то, что не писал про это только ленивый, подобная вещь очень важна.

• Загрузка из внешнего источника — это самый примитивный способ, проблема тут только в том, чтобы найти подходящий. Если упорства не хватило, то календарь можно самостоятельно сформировать в MS Excel.

Рвав-рвав, правильно гуглить — тоже труд, и лично мне без упорства никуда, знаете, сколько терпения нужно по кустам шариться?

• Формирование при помощи Power Query — тут все зависит от собственных навыков, можно либо написать кучу строчек в расширенном редакторе, либо использовать встроенные конструкции меню «Преобразование», которых может и не хватить.

Рвав-рвав, способ не совсем для тех, кто только «встал на лапки», как я на заглавном фото, тут мне 2-х месяцев нет).

• Формирование при помощи DAX — это тот самый способ, который мы планируем рассмотреть подробно, поскольку он наиболее часто используется в нашей работе. Навыки тут тоже нужны, без этого никуда, но затраты времени до получения конечного результата существенно меньше.

Рвав-рвав, конечно, каждый свою дорогу выбирает сам, как говорится, хозяин — барин.

• Получение базового набора дат, то есть списка значений с датами.
• На основании полученного списка, в зависимости от ваших потребностей, далее необходимо добавить необходимые аналитические разрезы, например, год, номер и название квартала, номер и название месяца. В принципе — это минимально необходимый набор, который используют все.
• Дополнительно можно добавить градацию по неделям (может пригодиться для создания платежного календаря), или дням недели (бывает нужно при логистике, посменной работе и так далее).

• При создании календаря правильно будет поместить его в отдельную таблицу, создание которой можно осуществить средствами Power BI при помощи кнопки «Создать таблицу», расположенной на вкладке «Моделирование».
• Если при создании календаря в Power BI уже были предварительно загружены какие-то данные, содержащие даты, то самое простое — это использовать функцию CALENDARAUTO, при этом список дат будет сгенерирован автоматически за весь период данных, созданная таблица будет иметь один столбец с типом «Дата/Время», полученные значения по умолчанию будут иметь следующий вид: «01.01.2019 0:00:00».

Рвав-рвав, особенностью работы данной функции является то, что в случае наличия в данных пустых значений дат ваш календарь будет сформирован, как говорится, «от царя Гороха», что, согласитесь, представляет некоторое неудобство.

• В тех случаях, когда предварительно загруженных данных, содержащих даты, нет, или если вам необходимо сформировать набор дат за неполный период времени, можно использовать функцию CALENDAR. Параметрами данной функции являются «start_date» и «end_date», то есть начальная и конечная дата периода.
• Однако использовать конструкцию «в лоб», написав формулу вида «Таблица = CALENDAR (01.01.2019, 31.12.2019)» не получится, поскольку значения необходимо обработать дополнительно при помощи функции DATE.
• При помощи встроенных преобразований, конечно, можно получить нужный результат, переписав формулу, указанную выше, следующим образом:

• Однако, поскольку собака Смайл «старый солдат», то итоговая формула для получения списка дат по 2019-му году будет выглядеть следующим образом:

• Как только таблица сформирована, необходимо установить нужный тип данных, а также, в целях дополнительного удобства, можно переименовать получившуюся таблицу с именем «Таблица» — в «Календарь», а ее единственный столбец «Date» — в «Дата».

• 1-й способ — использование встроенной иерархии дат;
• 2-й способ — использование выделенной функции;
• 3-й способ — использование универсальной функции;
• 4-й способ — использование комбинации различных функций.

• Формула для получения значения года выглядит следующим образом:

• Формула для получения номера квартала:

• Формула для получения названия квартала:

• Формула для получения номера месяца:

• Формула для получения названия месяца:

Рвав-рвав, как вы, наверно, уже поняли, подобным образом мы получили минимальный набор временных разрезов, о котором упоминалось выше. Лично я за этот способ агитирую всеми лапами, поскольку он очень прост, и как раз для «юных падаванов», однако, указанный способ имеет как достоинства, так и недостатки. Пожалуй, нужно не забыть написать статейку по этому поводу.

• Проверить получившийся результат:

• Формула для получения года:

• Формула для получения номера месяца:

• Формула для получения номера недели в году (2-й параметр — это начало недели, так как в России неделя начинается с понедельника, то значение равно «2»):

• Формула для получения номера дня в неделе (2-й параметр — это выбор номера дня и начала недели, так как в России 1-й день недели — понедельник, то значение равно «2»):

Рвав-рвав, выделенных функций довольно мало, поэтому, если 1-й метод не устраивает, можно воспользоваться либо универсальной функцией расчета дополнительных значений (вариант № 3), либо использовать другие способы.

• Для получения номера квартала можно использовать следующую формулу:

Рвав-рвав, на момент написания статьи формула, указанная выше, была одним из «обходных» способов получения номера квартала, сейчас есть функция «QUARTER».

• Проверить получившийся результат:

• Формула для получения года:

• Формула для получения номера квартала:

• Формула для получения номера месяца:

• Формула для получения названия месяца:

• Проверить получившийся результат:

• Чтобы не собирать календарь по отдельным столбцам, можно при создании таблицы использовать следующую формулу:

• Проверить получившийся результат:

• При таком способе создания календаря необходимо не забыть присвоить правильные типы данных.

Рвав-рвав, с календарем мы, наконец, закончили. Пойду игрушку распотрошу, чтоб хозяину веселее было!

Ваш Смайл