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 должна быть уверенность, что ваши данные корректны.

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

    Table =
    CALENDARAUTO ()

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

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

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

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

    Table =
    CALENDAR ( DATE ( 2019; 01; 01 ); DATE ( 2019; 12; 31 ) )

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

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

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

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

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

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

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

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

    Year =
    'Calendar'[Date].[Year]

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

    Quarter_Number =
    'Calendar'[Date].[QuarterNo]

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

    Quarter_Name =
    'Calendar'[Date].[Quarter]

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

    Month_Number =
    'Calendar'[Date].[MonthNo]

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

    Month_Name =
    'Calendar'[Date].[Month]

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

    art_001_screen

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

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

    Year =
    YEAR ( 'Calendar'[Date] )

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

    Month_Number =
    MONTH ( 'Calendar'[Date] )

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

    Month_Number =
    MONTH ( 'Calendar'[Date] )

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

    Week_Number =
    WEEKNUM ( 'Calendar'[Date]; 2 )

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

    Weekday_Number =
    WEEKDAY ( 'Calendar'[Date]; 2 )

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

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

    Quarter_Name =
    ROUNDUP ( DIVIDE ( MONTH ( 'Calendar'[Date] ); 3 ); 0 )

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

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

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

    Year =
    FORMAT ( 'Calendar'[Date]; "YYYY" )

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

    Quarter_Number =
    FORMAT ( 'Calendar'[Date]; "Q" )

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

    Month_Number =
    FORMAT ( 'Calendar'[Date]; "MM" )

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

    Quarter_Number = 
    FORMAT ( 'Calendar'[Date]; "MMMM" )

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

    Ваш Смайл