Будущее выглядит очень интересным с этой новой функцией.
Делиться
Введение
В прошлом я много писал о Time Intelligence в DAX.
Однако новая функция Time Intelligence на основе календаря меняет правила, поскольку некоторые концепции изменятся, а требуемые методы станут проще, чем раньше.
В любом случае, функции временного интеллекта останутся прежними, хотя нам придётся использовать их немного иначе. Но почти всё станет проще, чем раньше.
В этой статье я рассмотрю три варианта использования, для которых мне пришлось бы написать сложный код DAX.
Теперь давайте обсудим варианты использования.
Варианты использования
Финансовый календарь
При работе с финансовыми данными может потребоваться учёт более 12 месяцев для управления дополнительными бронированиями. Некоторые компании использовали такие дополнительные месяцы для обеспечения согласованности с обычными бронированиями.
До сих пор это было возможно только с помощью специального кода DAX.
Теперь мы можем создать собственный календарь и использовать с ним стандартные функции анализа времени.
Этот вариант использования имеет следующие требования:
- Мы должны охватить 15 месяцев в году.
- Нам нужны два представления: одно для месяцев бронирования и одно, где дополнительные месяцы сопоставляются с декабрем.
- Мы смотрим на данные PY; мы должны видеть все дни, включая високосные годы (29 февраля).
Еженедельные расчеты
Я уже писал о еженедельных расчётах. Для этого потребовался специальный код DAX, поскольку функции WDT не было, и рассчитать предыдущий год по неделям было невозможно.
Опять же, теперь это можно легко сделать с помощью календаря, содержащего информацию о неделе, а также новых функций TOTALWTD() и DATESWTD().
Еженедельный расчет по финансовым годам
Еженедельные расчеты становятся еще более сложными, когда нам приходится учитывать финансовые годы, которые не совпадают с календарными годами.
Даже такой сценарий теперь может быть решен с помощью стандартных функций анализа времени, если таблица календаря содержит правильную информацию.
Предпосылки
Первым условием является включение функции предварительного просмотра:
Другим предварительным условием является создание таблицы дат, охватывающей требуемые периоды.
Как и прежде, грамотно составленная таблица дат является ключевым фактором для работы с Time Intelligence, а теперь, с появлением новой функции, это становится еще важнее.
При работе с новыми возможностями Time Intelligence нам необходимо сделать три шага:
- Мы строим таблицу дат и определяем, какие столбцы нам нужны для каждого варианта использования.
- Затем мы определяем календарь для этой таблицы, назначая столбцы периодам, например, году, кварталу, месяцам, неделям и датам.
- Создайте показатели DAX, используя календарь, определенный на шаге 2.
Первые два шага одинаково важны, поскольку таблица должна быть тщательно составлена, чтобы охватить требуемые периоды.
Определение календаря позволяет нам использовать столбцы в таблице дат для назначения им предопределённых категорий. Скоро вы поймёте, что это значит.
Я опишу содержимое таблицы дат с примерами данных для каждого варианта использования.
Случай 1: Финансовый календарь
Прежде всего я создаю таблицу, содержащую следующую информацию:
- Годы
- Семестры (1 и 2 плюс третий для дополнительных месяцев)
- Кварталы (с 1 по 4 и пятый квартал для дополнительных месяцев)
- Месяц 1–15
- Названия всех 15 месяцев (янв.–дек. и дополнительные месяцы 1–3)
- 31 день в каждом месяце, даже в тех месяцах, в которых обычно меньше 31 дня
- Квартал и месяцы, где дополнительные месяцы сопоставляются с декабрем
Вот как это выглядит с двумя примерами на столбец:
| Имя столбца | Пример |
|---|---|
| ID_Дата | 20060101 20061301 |
| Дата_Реальная | 2006.01.01 Н/Д |
| Год | 2006 |
| ID_месяц | 200601 200613 |
| Месяц | 1 13 |
| День | 1 |
| Дата_немецкая | 01.01.2006 01.13.2006 |
| Дата_EN | 01/01/2006 13.01.2006 |
| НазваниеМесяца | Январь Дополнительный месяц 1 |
| МесяцНазваниеКраткое | Янв Добавить месяц 1 |
| ГодМесяцНазвание | Январь 2006 г. Дополнительный месяц 1 2006 г. |
| ГодМесяцИмяКраткое | Январь 2006 г. Добавить месяц 1 2006 |
| Семестр | 1 3 |
| Название семестра | Семестр 1 Семестр 3 |
| ГодСеместр | 20061 20063 |
| ГодСеместрНазвание | 1 семестр 2006 г. 3 семестр 2006 г. |
| Четверть | 1 5 |
| QuarterName | Квартал 1 Квартал 5 |
| ГодКвартал | 20061 20065 |
| ГодКварталНазвание | 1 квартал 2006 г. 5 квартал 2006 г. |
| КалендарьМесяц | 1 12 |
| CalendarMonthName | Январь декабрь |
| КалендарьМесяцНазваниеКраткое | Янв Декабрь |
| КалендарьГодМесяц | 200601 200612 |
| КалендарьГодМесяцНазвание | Январь 2006 г. Декабрь 2006 г. |
| КалендарьГодМесяцНазваниеКраткое | Январь 2006 г. Декабрь 2006 г. |
Два примера: один для января и один для первого дополнительного месяца.
Вот другой вид данных для дополнительных столбцов и строк:
В моем случае я создал эту таблицу на SQL, но это можно сделать на любом другом языке программирования, включая Power Query.
Вам нужно всего лишь иметь несколько списков чисел и объединить их.
Пример объединения таблиц со списками чисел вы можете найти в этой статье.
Но главное, что я могу свободно определять содержимое календаря. Даже столбец «Дата» не обязательно должен содержать реальные даты, поскольку в моём случае там только строки.
После импорта нового календаря в Power BI мы можем открыть новое диалоговое окно «Параметры календаря», щелкнув таблицу:
Теперь я создам два новых календаря.
Обратите внимание, что я не могу установить новую таблицу как таблицу дат, поскольку она содержит несуществующие «даты», например, 30 февраля.
Я нажимаю «Добавить категорию», чтобы добавить, например, год, месяц и месяц года, и назначаю им столбцы с данными:
При настройке календаря не забудьте нажать кнопку «Проверить данные» после добавления каждой категории. Это поможет вам обнаружить ошибки в данных, если они есть.
Эта кнопка проверяет, имеет ли каждое значение отношение «многие к одному» к каждому значению в категории выше.
Например, каждый месяц должен относиться к одному году. Категория «Месяц» должна содержать месяц и год, а категория «Месяц года» — только месяц.
В качестве основного столбца я выбрал столбцы идентификаторов, а в качестве ассоциированных столбцов — столбцы наименований с различными форматами и языками.
Подробную информацию о работе этой функции можно найти по ссылкам в разделе «Ссылки» ниже.
Чтобы иметь календарь, в котором дополнительные месяцы привязаны к декабрю, я создал следующий календарь:
Я не настраивал колонки для квартала в «реальном» календаре, потому что в моем сценарии они не нужны.
Для расчета годового дохода (PY) для онлайн-продаж я могу использовать функцию SAMEPERIODLASTYEAR(). Но вместо столбца «Дата» я передаю название финансового календаря:
Онлайн-продажи Fin PY = CALCULATE([Сумма онлайн-продаж], SAMEPERIODLASTYEAR('Финансовый_календарь'))
Если посмотреть на результаты за високосный год, то получится следующее:
А если посмотреть на результаты за дополнительные месяцы, то получится следующее:
Как видите, годовой доход рассчитан правильно для дополнительных месяцев.
Кроме того, в правой таблице вы можете увидеть значения за декабрь и дополнительные месяцы (из левой таблицы), добавленные к декабрю с использованием «реального» календаря.
Наличие такого простого решения является новаторским.
Теперь давайте рассмотрим еженедельные расчеты.
Случай 2: Еженедельный расчет
На этот раз я хочу рассчитать PY за неделю.
Поскольку я уже показал, как определять пользовательские календари, я покажу вам только содержимое задействованных столбцов и то, как они назначаются календарю.
На этот раз я использовал существующие столбцы из моей таблицы дат:
| ГодНедели | WeekKey | Год/Неделя | Неделя | Дата | День недели | Название дня недели |
|---|---|---|---|---|---|---|
| 2025 | 202501 | 2025/1 | 1 | 30/12/2024 | 1 | Понедельник |
| 2025 | 202501 | 2025/1 | 1 | 31/12/2024 | 2 | Вторник |
| 2025 | 202501 | 2025/1 | 1 | 01.01.2025 | 3 | Среда |
| 2025 | 202501 | 2025/1 | 1 | 02/01/2025 | 4 | Четверг |
| 2025 | 202501 | 2025/1 | 1 | 03/01/2025 | 5 | Пятница |
| 2025 | 202501 | 2025/1 | 1 | 04/01/2025 | 6 | Суббота |
| 2025 | 202501 | 2025/1 | 1 | 05/01/2025 | 7 | Воскресенье |
| 2025 | 202552 | 2025/52 | 52 | 22/12/2025 | 1 | Понедельник |
| 2025 | 202552 | 2025/52 | 52 | 23/12/2025 | 2 | Вторник |
| 2025 | 202552 | 2025/52 | 52 | 24/12/2025 | 3 | Среда |
| 2025 | 202552 | 2025/52 | 52 | 25/12/2025 | 4 | Четверг |
| 2025 | 202552 | 2025/52 | 52 | 26/12/2025 | 5 | Пятница |
| 2025 | 202552 | 2025/52 | 52 | 27/12/2025 | 6 | Суббота |
| 2025 | 202552 | 2025/52 | 52 | 28/12/2025 | 7 | Воскресенье |
Как видите, столбец [YearOfWeek] привязан к неделе, а не к календарному году. Я сделал это, чтобы гарантировать правильное назначение недели году. Без этого проверка календаря не пройдёт, поскольку столбец [WeekKey] для первой календарной недели каждого года будет привязан к двум разным годам.
Это показывает, насколько важно создать последовательную календарную таблицу.
Вот определение недельного календаря:
А вот результаты измерения с использованием этого календаря:
Как и прежде, мера использует простой вызов SAMEPRIODLASTYEAR() и использует недавно созданный «еженедельный календарь»:
Онлайн-продажи за неделю (PY) = CALCULATE([Сумма онлайн-продаж] ,SAMEPERIODLASTYEAR( 'Еженедельный календарь' ) )
Сравните это со сложным кодом, который у вас был до внедрения этой новой функции для расчета последовательного еженедельного показателя PY.
Вот результаты измерения с использованием новых показателей WTD:
Вот используемые меры:
Онлайн-продажи WTD = VAR WtdDates = DATESWTD('Еженедельный календарь') RETURN CALCULATE([Сумма онлайн-продаж] ,WtdDates ) Онлайн-продажи PY WTD = CALCULATE([Онлайн-продажи WTD] ,SAMEPERIODLASTYEAR('Еженедельный календарь') )
Почти невероятно, насколько легко создавать эти меры.
Случай 3: Еженедельный расчет с учетом финансовых лет
Этот вариант более сложный.
В этом случае финансовый год начинается первого августа.
Это означает, что первой неделей финансового года является неделя с первым днем финансового года.
Я настроил все столбцы в таблице «Даты». Вот выборка необходимых столбцов:
| Финансовый год за неделю | Финансовый год Неделя Сортировка | FiscalWeekSort | Финансовая неделя/год | Финансовая неделя | Дата | Финансовый день недели | Название дня недели |
|---|---|---|---|---|---|---|---|
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 28/07/2025 | 1 | Понедельник |
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 29/07/2025 | 2 | Вторник |
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 30/07/2025 | 3 | Среда |
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 31/07/2025 | 4 | Четверг |
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 01/08/2025 | 5 | Пятница |
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 02/08/2025 | 6 | Суббота |
| 25/26 | 252601 | 1 | 1 – 25/26 | 1 | 03/08/2025 | 7 | Воскресенье |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 20/07/2026 | 1 | Понедельник |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 21/07/2026 | 2 | Вторник |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 22/07/2026 | 3 | Среда |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 23/07/2026 | 4 | Четверг |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 24/07/2026 | 5 | Пятница |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 25/07/2026 | 6 | Суббота |
| 25/26 | 252652 | 52 | 52 – 25/26 | 52 | 26/07/2026 | 7 | Воскресенье |
Опять же, мне нужно добавить дополнительную колонку для финансового года, назначенную неделям.
Но на этот раз мне нужно создать отдельную таблицу с необходимыми столбцами. По какой-то причине использование этих столбцов из таблицы Date не работает. Любая попытка использовать эти столбцы приводила к странному эффекту.
Подробнее об этом можно прочитать здесь.
В итоге я добавил расчетную таблицу с необходимыми столбцами:
Дата финансовой недели = CALCULATETABLE( SUMMARIZECOLUMNS( 'Дата'[FiscalYearForWeek] ,'Дата'[Финансовая неделя/год] ,'Дата'[Сортировка финансовой недели] ,'Дата'[Название дня недели] ,'Дата'[День недели] ,'Дата'[Дата] ,'Дата'[Ключ даты]) ,NOT ISBLANK('Дата'[FiscalYearForWeek] ) )
Календарь, созданный на этой таблице, выглядит так:
Метод расчета объема продаж за предыдущий год, опять же, прост:
Онлайн-продажи PY (финансовая неделя) = CALCULATE([Сумма онлайн-продаж] ,SAMEPERIODLASTYEAR('Календарь финансовой недели') )
Вот результаты:
Видно, что результат идеально совпадает с неделей и днем недели предыдущего года, хотя даты и смещены.
Это именно то, чего я ожидал.
Заключение
Эта новая функция меняет все, что касается Time Intelligence с DAX.
Но, хотя мы можем упростить наши показатели DAX, необходимо проявлять особую осторожность при составлении таблиц дат. Всё дело в правильном содержании.
Интересно, что Microsoft уже рекомендует использовать эту функцию, хотя она доступна всего несколько месяцев с момента ее появления в качестве предварительной версии.
Я рекомендую вам изучить этот вопрос. Прочитайте статьи по ссылкам ниже. Протестируйте его в своих конкретных сценариях и решите, стоит ли использовать эту функцию в существующих решениях.
Я обязательно воспользуюсь этой функцией при запуске новых решений.
Единственный недостаток заключается в том, что это может увеличить количество таблиц дат в модели данных. До сих пор я использовал одну центральную таблицу дат для всего. Теперь мне, возможно, потребуется создать отдельные таблицы дат для конкретных сценариев. Но это может усложнить комбинирование различных аспектов модели данных. Это создаст дополнительные трудности при интерпретации данных.
Задумайтесь об этом на мгновение:
Действительно ли хорошая идея размещать два разных календаря на одной странице? Будут ли результаты при этом сопоставимы? Может ли это запутать ваших потребителей?
Я буду категорически избегать подобных ситуаций. Сравнивать результаты по месяцам и неделям на одних и тех же страницах или даже в одном отчёте, на мой взгляд, бессмысленно.
Следите за новостями, чтобы узнать больше об этой теме. Напишу об этом подробнее со временем, когда столкнусь с интересными ситуациями.
Ссылки
Ниже представлена документация Microsoft по аналитике времени на основе календаря: Реализация вычислений на основе времени в Power BI – Power BI | Microsoft Learn.
В этой статье SQL BI эта новая функция описывается очень подробно: Знакомство с аналитикой времени на основе календаря в DAX – SQLBI.
Как и в предыдущих статьях, я использую пример набора данных Contoso. Вы можете бесплатно скачать набор данных ContosoRetailDW с сайта Microsoft здесь.
Данные Contoso можно использовать свободно по лицензии MIT, как описано в этом документе. Я изменил набор данных, чтобы сместить данные на современные даты.
Источник: towardsdatascience.com
