Post-Image

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

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

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

Календарь в Power BI можно создать, по большому счету, 3-мя различными способами:

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

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

  • Формирование при помощи Power Query – тут все зависит от собственных скилов, можно либо написать кучу строчек в расширенном редакторе, либо использовать встроенные конструкции меню “Transform” (“Преобразование”), которых может и не хватить.

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

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

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

    Построение календаря, основанного на DAX-формулах, состоит из следующих этапов:

  • Получение базового набора дат, то есть списка значений с датами.

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

  • Дополнительно можно добавить градацию по неделям (может пригодиться для создания платежного календаря), или дням недели (бывает нужно при логистике, посменной работе и пр.).

    При получении основного набора дат желательно учесть следующие моменты:

  • При создании календаря правильно будет поместить его в отдельную таблицу, создание которой можно осуществить средствами Power BI при помощи кнопки “New Table” (“Создать таблицу”), расположенной на вкладке “Modeling” (“Моделирование”).

  • Если при создании календаря в Power BI уже были предварительно загружены какие-то данные, содержащие даты, то самое простое – это использовать функцию “CALENDARAUTO”, при этом список дат будет сгенерирован автоматически за весь период данных, созданная таблица будет иметь один столбец с типом “Date/Time” (“Дата/Время”), полученные значения по умолчанию будут иметь следующий вид: “01.01.2019 0:00:00”

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

    Поэтому, при использовании функции “CALENDARAUTO” должна быть уверенность, что ваши данные корректны.

    Синтаксис данной функции:

    Таблица =
    CALENDARAUTO ()

    Тип данных, если время вам не нужно, лучше поменять на “Date” (“Дата”), созданный столбец по умолчанию будет иметь имя “Date” (“Дата”).

  • В тех случаях, когда предварительно загруженных данных, содержащих даты, нет, или если вам необходимо сформировать набор дат за неполный период времени, можно использовать функцию “CALENDAR”. Параметрами данной функции являются “start_date” и “end_date”, то есть начальная и конечная дата периода.

    Однако использовать конструкцию “в лоб”, написав формулу Таблица = CALENDAR ( 01.01.2019, 31.12.2019 ) не получится, значения необходимо обработать дополнительно при помощи функции “DATE”.

    Итоговая конструкция для получения списка дат по 2019-му году будет выглядеть следующим образом:

    Таблица =
    CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2019, 12, 31 ) )

    Как только расчеты произведены, необходимо установить нужный тип данных, а также, в целях дополнительного удобства, можно переименовать получившуюся таблицу с имененм “Таблица” – в “Календарь”, а ее единственный столбец “Date” – в “Дата”.

    Для наполнения получившейся таблицы нужными временными разрезами существует несколько вариантов:

  • 1-й способ – использование встроенной иерархии дат;

  • 2-й способ – использование “выделенной” функции;

  • 3-й способ – использование универсальной функции.

    Дополнительные временные разрезы в таблицу “Календарь” можно добавить путем создания необходимого количества расчетных столбцов при помощи кнопки “New Column” (“Создать столбец”), расположенной на вкладке “Modeling” (“Моделирование”).

    1-й способ – использование встроенной иерархии дат:

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

    Год =
    'Календарь'[Дата].[Год]

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

    Номер квартала =
    'Календарь'[Дата].[№Квартала]

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

    Название квартала =
    'Календарь'[Дата].[Квартал]

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

    Номер месяца =
    'Календарь'[Дата].[№Месяца]

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

    Название месяца =
    'Календарь'[Дата].[Месяц]

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

    art_001_screen

    2-й способ – использование “выделенной” функции:

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

    Год =
    YEAR ( 'Календарь'[Дата] )

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

    Номер месяца =
    MONTH ( 'Календарь'[Дата] )

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

    Номер недели в году =
    WEEKNUM ( 'Календарь'[Дата], 2 )

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

    Номер дня в неделе =
    WEEKDAY ( 'Календарь'[Дата], 2 )

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

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

    Номер квартала =
    ROUNDUP ( DIVIDE ( MONTH ( 'Календарь'[Дата] ), 3 ), 0 )

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

    3-й способ – использование универсальной функции:

    В качестве универсальной функции для формирования календаря можно воспользоваться функцией “FORMAT”. Ниже представлены несколько примеров ее использования, поскольку предопределенных и пользовательских форматов чисел, даты и времени великое множество.

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

    Год =
    FORMAT ( 'Календарь'[Дата], "YYYY" )

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

    Номер квартала =
    FORMAT ( 'Календарь'[Дата], "Q" )

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

    Номер месяца =
    FORMAT ( 'Календарь'[Дата], "MM" )

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

    Название месяца = 
    FORMAT ( 'Календарь'[Дата], "MMMM" )

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

    Ваш Смайл