В этой статье описывается ссылочная таблица дат в DAX с использованием шаблона Power BI. Такой же метод можно использовать в моделях Analysis Services. Загрузите последнюю версию шаблона на странице Dax Date Template.

Зачем нужна ссылочная таблица дат

Функция Auto Date / Time, доступная в Power BI, представляет несколько ограничений:

• Она имеет фиксированный набор строк.
• Она не обрабатывает финансовые годы.
• Она не включает недели.
• Она не может использоваться для разных таблиц в одной и той же модели данных.

Как правило, необходимо отключить эту функцию и создать пользовательскую таблицу дат. Эта задача является повторяющейся и отнимает много времени. Создание новых моделей Power BI, начиная с шаблона Power BI, содержащего полнофункциональную таблицу Date, избавляет пользователя от записи требуемого выражения DAX, - так же как при настройке необходимых свойств для определения формата отображения, иерархии и видимости столбцов, требуемых в отчетах и расчетах.

Что представляет собой файл шаблона Power BI (PBIT)

Файл с расширением .PBIT является файлом шаблона Power BI, который не содержит данные и импортирует или генерирует данные при его открытии. Любой файл Power BI можно сохранить как файл шаблона. Все таблицы, включенные в модель данных, сохраняются как пустые таблицы. Они заполняются при открытии шаблона, считывая данные из источника данных.

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

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

Как использовать шаблон ссылочной таблицы дат

Откройте файл Date template.pbit, чтобы создать новый проект Power BI. Модель содержит одну вычисленную таблицу с именем Date. Она содержит все дни, которые существуют в течение нескольких лет, определенные двумя параметрами конфигурации - FirstYear и LastYear.

Начальный раздел расчетной таблицы содержит несколько параметров, определяющих, как генерируется таблица Date. Столбцы разделены на несколько разделов:

• Базовые столбцы дат
• Включают  основную информацию, такую как дата, день недели, день месяца
• Солнечный календарь (префикс Calendar)
• Столбцы для стандартного ежемесячного календаря “январь-декабрь”
• Финансовый ежемесячный календарь (префикс Fiscal)
• Столбцы для финансового ежемесячного (григорианского) календаря, где финансовый год начинается в первый день месяца, который не является январем (см. Параметры)
• Финансовый еженедельный календарь (префикс FW)
• Столбцы для финансового еженедельного календаря, где год начинается в определенный день, следуя одному из поддерживаемых правил (см. Параметры)
• Поддержка ISO, 4-4-5, 4-5-4, 5-4-4 недельных календарей
• Праздники и рабочие дни
• Столбцы для праздников и рабочих дней, в зависимости от праздников в стране, определенной в параметрах

Выбор столбцов

Заключительная часть выражения DAX представляет собой инструкцию SELECTCOLUMN, которая включает все столбцы, используемые в шаблоне. Можно настроить таблицу Date, удалив или комментируя нежелательные столбцы из инструкции SELECTCOLUMNS. Столбцы, удаленные из расчетной таблицы DAX, также удаляются из таблицы Date, также удаляя связанные иерархии.

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

Power BI отображает ограниченное количество строк в редакторе. Используйте инструмент, такой как DAX Editor, для изменения выражения DAX таблицы Date. Используйте copy и paste для перемещения кода между Power BI и внешним редактором.

Ссылка на столбцы

Соглашение об именах, используемое для имен столбцов, следующее:

• Полные имена с пробелами (например, «Day of Month») - это видимые столбцы. Если столбец скрыт, он отображается через уровень иерархии.
• Имена в корпусе Pascal (например, «WeekDayNumber») - это скрытые столбцы, используемые для внутреннего расчета или используемые для сортировки других столбцов.

Основные столбцы таблицы  Date

Следующие примеры получены и описаны с использованием 21 марта 2018 года в качестве ссылки.

• Date: 3/21/2018 (Дата) – это дата в типе данных таблицы Date
• DateKey: 20180321 (Целое число) –  дата в целочисленном формате YYYYMMDD
• Day of Month: 21 (Целое число) – день месяца
• WeekDayNumber: 3 (Целое число) – день недели в цифровом формате, где 1 - первый день недели (зависит от параметров конфигурации)
• Week Day: Tue (Символьная строка) – название дня недели, три буквы
• порядковый365DayNumber: 43151 (Целое число) – количество дней с 30 декабря 1899 года, за исключением 29 февраля (полезно для расчета годового общего количества с 365 днями)

Солнечный календарь ( Calendar)

• Calendar YearNumber: 2018 (Целое число) – номер календарного года
• Calendar Year: 2018 (Символьная строка) – календарный год, отсортированный по Calendar YearNumber
• Calendar QuarterNumber: 1 (Целое число) – номер календарного квартала
• Calendar Quarter: Q1 (Символьная строка) – календарный квартал, отсортированный по Calendar QuarterNumber
• Calendar YearQuarterNumber: 8072 (Целое число) – порядковый номер квартала по годам (= [Calendar YearNumber] * 4 + [Calendar QuarterNumber] – 1)
• Calendar Quarter Year: Q1 2018 (Символьная строка) – квартал и год, отсортированный по Calendar YearQuarterNumber
• Calendar MonthNumber: 3 (Целое число) – номер месяца календаря, где 1 - январь
• Calendar Month: March (Символьная строка) – имя месяца календаря, отсортированное по Calendar MonthNumber
• Calendar YearMonthNumber: 24218 (Целое число) – порядковый номер месяца по годам (= [Calendar YearNumber] * 12 + [Calendar MonthNumber] – 1)
• Calendar Month Year: Mar 2018 (Символьная строка) – месяц и год, отсортированный по Calendar YearMonthNumber
• Calendar WeekNumber: 12 (Целое число) – номер календарной недели
• Calendar Week: W12 (Символьная строка) – календарная неделя, отсортированная по Calendar WeekNumber
• Calendar YearWeekNumber: 6116 (Целое число) – порядковый номер недели по годам. В неделе всегда 7 дней. Когда неделя переходит из декабря в январь, в обоих месяцах используется один и тот же номер недели.
• Calendar Week Year: W12-2018 (Символьная строка) –календарная неделя и год, отсортированные по Calendar WeekYearOrder (календарная неделя может иметь менее 7 дней в начале и конце года)
• Calendar WeekYearOrder: 201812 (Целое число) – календарный год и неделя в формате YYYYWW (календарная неделя может иметь менее 7 дней в начале и конце года)
• Calendar RelativeWeekPos: -8 (Целое число) – относительные недели по сравнению с «TodayReference» (см. Параметры); отрицательные значения для недель, находящихся до TodayReference, положительные значения для недель, находящихся после TodayReference
• Calendar RelativeMonthPos: -2 (Целое число) –  относительные месяцы по сравнению с «TodayReference» (см. Параметры); отрицательные значения для  месяцев, находящихся до TodayReference, положительные значения для месяцев, находящихся после TodayReference
• Calendar RelativeQuarterPos: 0 (Целое число) –  относительные кварталы по сравнению с «TodayReference» (см. Параметры); отрицательные значения для  кварталов, находящихся до TodayReference, положительные значения для кварталов, находящихся после TodayReference. 0 означает тот же квартал, что и TodayReference
• Calendar RelativeYearPos: 0 (Целое число) – относительные годы по сравнению с «TodayReference» (см. Параметры); отрицательные значения для  годов, находящихся до TodayReference, положительные значения для годов, находящихся после TodayReference. 0 означает тот же год, что и TodayReference
• Calendar StartOfMonth: 3/1/2018 (Дата) – первый день месяца
• Calendar EndOfMonth: 3/31/2018 (Дата) – последний день месяца
• Calendar StartOfQuarter: 1/1/2018 (Дата) – первый день квартала
• Calendar EndOfQuarter: 3/31/2018 (Дата) – последний день квартала
• Calendar StartOfYear: 1/1/2018 (Дата) – первый день года
• Calendar EndOfYear: 12/31/2018 (Дата) – последний день года
• Calendar MonthDays: 31 (Целое число) – количество дней в месяце
• Calendar QuarterDays: 90 (Целое число) – количество дней в квартале
• Calendar YearDays: 365 (Целое число) – количество дней в году
• Calendar DayOfMonthNumber: 21 (Целое число) – порядковый номер дня в течение месяца (похож на столбец Day of Month)
• Calendar DayOfQuarterNumber: 79 (Целое число) –  порядковый номер дня в течение квартала
• Calendar DayOfYearNumber: 79 (Целое число) –  порядковый номер дня в течение года
• Calendar DatePreviousWeek: 3/14/2018 (Дата) –  тот же относительный день на предыдущей неделе
• Calendar DatePreviousMonth: 2/21/2018 (Дата) –  тот же относительный день в предыдущем месяце
• Calendar DatePreviousQuarter: 12/21/2017 (Дата) –  тот же относительный день в предыдущем квартале
• Calendar DatePreviousYear: 3/21/2017 (Дата) –  тот же относительный день в предыдущем году

Финансовый календарь (Fiscal)

Даты, используемые в качестве примеров, были получены с использованием TodayReference = 1/23/2018 и FiscalCalendarFirstMonth = 9. Таким образом, Q1 - сентябрь-ноябрь, Q2 - декабрь-февраль и т. д.

• Fiscal Year: F 2018 (Символьная строка) – финансовый год, отсортированный по Fiscal YearNumber
• Fiscal YearNumber: 2018 (Целое число) – номер финансового года
• Fiscal QuarterNumber: 3 (Целое число) – номер финансового квартала
• Fiscal Quarter: FQ3 (Символьная строка) – финансовый квартал, отсортированный по Fiscal QuarterNumber
• Fiscal YearQuarterNumber: 8074 (Целое число) – порядковый номер квартала по годам (= [Fiscal YearNumber] * 4 + [Fiscal QuarterNumber] – 1)
• Fiscal Quarter Year: FQ3 2018 (Символьная строка) – финансовый квартал и год, отсортированные по Fiscal YearQuarterNumber
• Fiscal MonthNumber: 7 (Целое число) – номер финансового месяца, где 1 - первый месяц в финансовом году
• Fiscal Month: Mar (Символьная строка) – имя финансового месяца, отсортированное по Fiscal MonthNumber
• Fiscal YearMonthNumber: 24222 (Целое число) – порядковый номер месяца по годам (= [Fiscal YearNumber] * 12 + [Fiscal MonthNumber] – 1)
• Fiscal Month Year: Mar 2018 (Символьная строка) – финансовый месяц и год, отсортированные по  Fiscal YearMonthNumber
• Fiscal WeekNumber: 30 (Целое число) – финансовый номер недели
• Fiscal Week: W30 (Символьная строка) – финансовая неделя, отсортированная по Fiscal WeekNumber
• Fiscal YearWeekNumber: 6116 (Целое число) – порядковый номер недели по годам (в неделе всегда 7 дней. Когда неделя переходит из декабря в январь, в обоих месяцах используется один и тот же номер недели.) - также как в Calendar YearWeekNumber
• Fiscal Week Year: FW30-2018 (Символьная строка) – финансовая неделя и год, отсортированные по Fiscal WeekYearOrder (календарная неделя может иметь менее 7 дней в начале и конце года)
• Fiscal WeekYearOrder: 201830 (Целое число) – финансовая неделя и год в формате YYYYWW(календарная неделя может иметь менее 7 дней в начале и конце года)
• Fiscal RelativeWeekPos: -8 (Целое число) – относительные недели по сравнению с “TodayReference” (см. Параметры); отрицательные значения для недель, находящихся до TodayReference, положительные значения для недель, находящихся после TodayReference
• Fiscal RelativeMonthPos: -2 (Целое число) – относительные месяцы по сравнению с “TodayReference” (см. Параметры); отрицательные значения для месяцев, находящихся до TodayReference, положительные значения для месяцев, находящихся после TodayReference
• Fiscal RelativeQuarterPos: -1 (Целое число) – относительные кварталы по сравнению с “TodayReference” (см. Параметры); отрицательные значения для кварталов, находящихся до TodayReference, положительные значения для кварталов, находящихся после TodayReference
• Fiscal RelativeYearPos: 0 (Целое число) –относительные годы по сравнению с “TodayReference” (см. Параметры); отрицательные значения для годов, находящихся до TodayReference, положительные значения для годов, находящихся после TodayReference
• Fiscal StartOfMonth: 3/1/2018 (Дата) – первый день финансового месяца
• Fiscal EndOfMonth: 3/31/2018 (Дата) – последний день финансового месяца
• Fiscal StartOfQuarter: 3/1/2018 (Дата) – первый день финансового квартала
• Fiscal EndOfQuarter: 5/31/2018 (Дата) – последний день финансового квартала
• Fiscal StartOfYear: 9/1/2017 (Дата) – первый день финансового года
• Fiscal EndOfYear: 8/31/2018 (Дата) – последний день финансового года
• Fiscal MonthDays: 31 (Целое число) – количество дней финансового месяца
• Fiscal QuarterDays: 92 (Целое число) – количество дней финансового квартала
• Fiscal YearDays: 365 (Целое число) – количество дней финансового года
• Fiscal DayOfMonthNumber: 21 (Целое число) – порядковый номер дня в течение финансового месяца (похож на столбец Day of Month)
• Fiscal DayOfQuarterNumber: 21 (Целое число) – порядковый номер дня в течение финансового квартала
• Fiscal DayOfYearNumber: 201 (Целое число) – порядковый номер дня в течение финансового года
• Fiscal DatePreviousWeek: 3/14/2018 (Дата) – тот же относительный день на прошлой финансовой неделе
• Fiscal DatePreviousMonth: 2/21/2018 (Дата) – тот же относительный день в прошлом финансовом месяце

• Fiscal DatePreviousQuarter: 12/21/2017 (Дата) – тот же относительный день в прошлом финансовом квартале
• Fiscal DatePreviousYear: 3/21/2017 (Дата) – тот же относительный день в прошлом финансовом году

Финансовый недельный календарь (FW)

Даты выборки были получены с помощью TodayReference = 1/23/2018, WeeklyType = Last, QuarterWeekType = 445 и FiscalCalendarFirstMonth = 9.

• FW YearNumber: 2018 (Целое число) – финансовый недельный номер года
• FW Year: FW 2017 (Символьная строка) – финансовый недельный год, отсортированный по FW YearNumber
• FW QuarterNumber: 3 (Целое число) – финансовый недельный номер квартала
• FW Quarter: FW Q3 (Символьная строка) – финансовый недельный квартал, отсортированный по FW QuarterNumber
• FW YearQuarterNumber: 8070 (Целое число) – порядковый номер квартала по годам (= [FW YearNumber] * 4 + [FW QuarterNumber] – 1)
• FW Quarter Year: FW Q3 2017 (Символьная строка) – финансовый недельный квартал и год, отсортированный по FW YearQuarterNumber
• FW MonthNumber: 7 (Целое число) – финансовый недельный номер месяца или периода, где 1 - первое число финансового недельный года
• FW Month: FW P07 (Символьная строка) – имя финансового недельного месяца или периода, отсортированное по Fiscal MonthNumber
• FW YearMonthNumber: 24210 (Целое число) – порядковый номер месяца или периода по годам(= [FW YearNumber] * 12 + [FW MonthNumber] – 1)
• FW Month Year: FW P07 2017 (Символьная строка) – финансовый недельный месяц и год, отсортированный по FW YearMonthNumber
• FW WeekNumber: 30 (Целое число) – финансовый недельный номер недели
• FW Week: FW W30 (Символьная строка) –финансовая недельная неделя, отсортированная по FW WeekNumber
• FW YearWeekNumber: 6117 (Целое число) – порядковый номер недели по годам (в неделе всегда 7 дней, а номер недели может варьироваться от 1 до 52)
• FW WeekYear: FW W30 2017 (Символьная строка) – финансовые  недельные неделя и год, отсортированные по FW YearWeekNumber
• FW StartOfWeek: 3/18/2018 (Дата) – первый день недели
• FW EndOfWeek: 3/24/2018 (Дата) – последний день недели
• FW RelativeWeekPos: -8 (Целое число) – относительные недели по сравнению с “TodayReference” (см. Параметры); отрицательные значения для недель, находящихся до TodayReference, положительные значения для недель, находящихся после TodayReference
• FW RelativeMonthPos: -1 (Целое число) –  относительные финансовые недельные месяцы или периоды по сравнению с “TodayReference” (см. Параметры); отрицательные значения для месяцев, находящихся до TodayReference, положительные значения для месяцев, находящихся после TodayReference
• FW RelativeQuarterPos: -1 (Целое число) – относительные финансовые недельные кварталы по сравнению с “TodayReference” (см. Параметры); отрицательные значения для кварталов, находящихся до TodayReference, положительные значения для кварталов, находящихся после TodayReference
• FW RelativeYearPos: 0 (Целое число) – относительные финансовые недельные годы по сравнению с “TodayReference” (см. Параметры); отрицательные значения для годов, находящихся до TodayReference, положительные значения для годов, находящихся после TodayReference
• FW StartOfMonth: 2/25/2018 (Дата) – первый день финансового недельного месяца или периода
• FW EndOfMonth: 3/24/2018 (Дата) – последний день финансового недельного месяца или периода
• FW StartOfQuarter: 2/25/2018 (Дата) – первый день финансового недельного квартала
• FW EndOfQuarter: 5/26/2018 (Дата) – последний день финансового недельного квартала
• FW StartOfYear: 8/27/2017 (Дата) – первый день финансового недельного года
• FW EndOfYear: 8/25/2018 (Дата) – последний день финансового недельного года
• FW MonthDays: 28 (Целое число) – количество дней финансового недельного месяца или периода
• FW QuarterDays: 91 (Целое число) – количество дней финансового недельного квартала
• FW YearDays: 364 (Целое число) – количество дней финансового недельного  года
• FW DayOfMonthNumber: 24 (Целое число) – порядковый номер дня месяца или периода финансового недельного календаря
• FW DayOfQuarterNumber: 24 (Целое число) – порядковый номер дня квартала финансового недельного календаря
• FW DayOfYearNumber: 206 (Целое число) – порядковый номер дня финансового недельного года
• FW DatePreviousWeek: 3/14/2018 (Дата) – тот же относительный день на прошлой неделе
• FW DatePreviousMonth: 2/14/2018 (Дата) – тот же относительный день прошлого финансового недельного месяца или периода
• FW DatePreviousQuarter: 12/20/2017 (Дата) – тот же относительный день прошлого финансового недельного квартала
• FW DatePreviousYear: 3/22/2017 (Дата) – S тот же относительный день прошлого финансового недельного года

Праздники и рабочие дни

• Holiday Name: (пробел) (Символическая строка) – Название праздника, (пробел), если это не праздник
• IsWorkingDay: True (Булево выражение) – True для рабочих дней, False для нерабочих дней, в зависимости от дня недели, праздников и конфигурации в параметрах
• Day Type: Рабочий день (Символическая строка) – Строка, отображающая «Рабочий день» или «Нерабочий день», в зависимости от дня недели, праздников и конфигурации в параметрах

Параметры

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

• TodayReference
• Значение по умолчанию: TODAY ()
• Определяет контрольную дату (например, «текущий день») в модели. Если есть данные за 5 дней до контрольной даты, можно изменить контрольную дату в модели, чтобы правильно установить все относительные вычисления недель / месяцев / кварталов / лет.
• FirstYear
• Значение по умолчанию: числовое значение, например. 2008
• Первый год, сгенерированный в таблице Date. Может быть присвоен динамический расчет, считывающий первый год данных, доступных в других таблицах.
• LastYear
• Значение по умолчанию: YEAR ( TodayReference )
• Прошлый год, сгенерированный в таблице Date.  Если модель данных настроена таким образом, чтобы она давала возможность для будущих дат, она должна быть настроена для чтения прошлогодних данных, доступных в других таблицах.
• FiscalCalendarFirstMonth
• Диапазон: число от 1 до 12
• Используется для финансового ежемесячного (григорианского) календаря и для финансового недельного календаря. Если установлено значение 1, финансовый ежемесячный календарь (Fiscal) совпадает с солнечным (Calendar). Однако финансовый недельный календарь (FW) всегда генерирует другой результат, даже если для этого параметра установлено значение 1.
• FirstDayOfWeek
• Диапазон: 0 - воскресенье, 1 - понедельник, 2 - вторник, ... 5 - пятница, 6 - суббота
• Определяет первый день недели и когда начинается неделя в недельном календаре. Календари США обычно используют 0 (воскресенье), тогда как в европейских календарях используется 1 (понедельник).
• IsoCountryHolidays
• Диапазон: ISO-код страны (она должна быть одной из поддерживаемых стран - США, CA, Великобритания, AU, DE, FR, IT, ES, NL, BE, PT)
• Определяет, какую страну использовать, чтобы установить праздничные дни в календаре в качестве нерабочих.
• WeeklyType
• Значение по умолчанию: Last
• Диапазон: “Last” или “Nearest”
• Устанавливает определение конца года для финансового недельного календаря (FW).
• Last: для последнего рабочего дня месяца в конце финансового года
• Nearest:  для последнего рабочего дня, ближайшего к концу месяца
• Для календарного использования ISO:
• FiscalCalendarFirstMonth = 1 (ISO всегда начинается в январе)
• FirstDayOfWeek = 1 (ISO всегда начинается в понедельник)
• WeeklyType = “Nearest” (ISO использует  тип алгоритма ближайшей недели)
• Для США с последней субботой месяца в конце финансового года
• FirstDayOfWeek = 0 (недели в США начинаются в воскресенье)
• WeeklyType = “Last”
• Для США с последней субботой, ближайшей к концу месяца
• FirstDayOfWeek = 0 (недели в США начинаются в воскресенье)
• WeeklyType = “Nearest”
• QuarterWeekType
• Значение по умолчанию: 445
• Диапазон: “445”, “454”, “544”
• Определяет количество недель за период в каждом квартале. Кварталы всегда считаются 13 неделями в финансовым недельном календаре (FW).
• CalendarRange
• Значение по умолчанию: Calendar
• Диапазон: “Calendar”, “FiscalGregorian”, “FiscalWeekly”
• Определяет, к какому типу календаря применяются границы года при генерации таблицы. Использование FiscalWeekly в первый и последний день года может не соответствовать первому и последнему дню месяца.
• CalendarGregorianPrefix
• Значение по умолчанию: “” (empty string)
• Префикс, используемый в столбцах солнечного григорианского календаря.
• FiscalGregorianPrefix
• Значение по умолчанию: F
• Префикс, используемый в столбцах финансового григорианского календаря.
• FiscalWeeklyPrefix
• Значение по умолчанию: “FW ”
• Префикс, используемый в столбцах финансового недельного календаря.
• WorkingDayType
• Значение по умолчанию: Working day
• Описание, используемое для рабочих дней (может быть переведено).
• NonWorkingDayType
• Значение по умолчанию: Non-working day
• Описание, используемое для нерабочих дней (может быть переведено).
• WeeklyCalendarType
• Значение по умолчанию: Weekly
• Диапазон: “Weekly”, “Custom”
• Используйте Weekly для автоматического создания финансового недельного календаря на основе FiscalCalendarFirstMonth, FirstDayOfWeek и WeeklyType. Используйте Custom для определения произвольного диапазона дат для каждого года, определенного в CustomFiscalPeriods.
• WorkingDays
• Значение по умолчанию: 1, 2, 3, 4, 5
• Определяет рабочие дни недели, с понедельника по пятницу по умолчанию (0 = воскресенье, 6 = суббота). В определении должно использоваться синтаксис DATATABLE.
• CustomFiscalPeriods
• Значение по умолчанию:  см. пример кода
• Определяет настраиваемый список финансовых периодов, которые могут отменить стандартное генерирование еженедельных календарей. В определении должно использоваться синтаксис DATATABLE.

No Comments