В прошлом уроке мы разобрали механизм calculation groups. Теперь — его главное, «убийственное» применение: time intelligence. Именно временные расчёты плодят больше всего почти одинаковых мер, и именно их выгоднее всего собрать в один набор. Опираемся на time intelligence calc group в SQLBI.
Базовые меры [Выручка], [Маржа], [Количество] и помеченная как таблица дат Календарь (как в уроке про календарь, pro-курс). На них построим один TI-набор.
Почему именно TI
Без calculation groups каждый временной вариант пишут отдельной мерой — и для каждой базовой меры:
Выручка YTD, Выручка PY, Выручка YOY, Выручка YOY %, Выручка PYTD …
Маржа YTD, Маржа PY, Маржа YOY, …
Количество YTD, …3 базовые меры × ~10 временных вариантов = 30 мер с дублированной логикой. Добавили четвёртую базовую — пишите ещё 10. Это не масштабируется. TI-calculation group решает ровно это: логика времени описывается один раз и применяется к любой мере.
TI-набор пунктов
Создаём группу «Время» и наполняем её пунктами. Внутри каждого текущая мера обозначается SELECTEDMEASURE() (прошлый урок):
Текущий = SELECTEDMEASURE ()
YTD = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( Календарь[Дата] ) )
QTD = CALCULATE ( SELECTEDMEASURE (), DATESQTD ( Календарь[Дата] ) )
MTD = CALCULATE ( SELECTEDMEASURE (), DATESMTD ( Календарь[Дата] ) )
PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( Календарь[Дата] ) )
PY YTD = CALCULATE ( SELECTEDMEASURE (),
DATESYTD ( SAMEPERIODLASTYEAR ( Календарь[Дата] ) ) )
YOY =
VAR _Cur = SELECTEDMEASURE ()
VAR _Py = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( Календарь[Дата] ) )
RETURN _Cur - _Py
YOY % =
VAR _Cur = SELECTEDMEASURE ()
VAR _Py = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( Календарь[Дата] ) )
RETURN DIVIDE ( _Cur - _Py, _Py )
Скользящие 12 мес = CALCULATE ( SELECTEDMEASURE (),
DATESINPERIOD ( Календарь[Дата], MAX ( Календарь[Дата] ), -12, MONTH ) )Один набор из ~10 пунктов — и любая мера получает все эти варианты. Положите пункты группы в столбцы матрицы, базовую меру — в значения: на пересечении Power BI применит нужный пункт к выбранной мере.
SELECTEDMEASURE() делает пункт независимым от конкретной меры: «PY YTD» одинаково оборачивает и выручку, и маржу, и количество. Поэтому 10 пунктов закрывают временные варианты для всех базовых мер сразу, а новая базовая мера автоматически получает весь TI-набор — без единой новой меры. Ни один другой приём так не сворачивает дублирование.
Две детали, без которых не работает
1. Помеченная таблица дат. Все функции DATESYTD/SAMEPERIODLASTYEAR/… требуют корректную таблицу дат, помеченную Mark as date table, с непрерывным диапазоном (урок про календарь в pro-курсе). Без неё time intelligence молча врёт или ломается.
2. Динамический формат для процентов. Пункты YTD, PY наследуют формат базовой меры (рубли). А YOY % — это проценты, и формат надо переопределить выражением формата у пункта:
-- Format String Expression для пункта «YOY %»
"0.0%"Так [Выручка] в пункте «Текущий» покажется «1 234 ₽», а в «YOY %» — «12,3%». Это та самая dynamic format string из прошлого урока, и для TI-набора она обязательна.
Сортировка пунктов
Пункты по умолчанию идут по алфавиту — «MTD, PY, YTD» вперемешку. Задайте каждому Ordinal (порядковый номер), чтобы в матрице они шли логично: Текущий → YTD → QTD → MTD → PY → PY YTD → YOY → YOY %. Сортировка пунктов — отдельный столбец порядка, ровно как «Месяц по номеру месяца».
Вторая группа: отклонения поверх первой
В TI-наборе выше «YOY» и «YOY %» вшиты прямо в пункты периода. Но отклонение к прошлому году нужно ведь не только для «Текущего», а для любого периода: «YTD к ПГ, %», «скользящие 12М, Δ абс». Вшивать вариант сравнения в каждый период — снова комбинаторный взрыв (период × способ сравнения). Решение — вторая calculation group, которая работает поверх первой.
Делаем чистое разделение. Первая группа «Период» отвечает только за то, какой период показать (Текущий, YTD, QTD, MTD, PY, скользящие 12М — без YOY). Вторая группа «Сравнение» отвечает за то, как сравнить:
-- Группа «Сравнение»
Значение = SELECTEDMEASURE ()
Δ к ПГ, абс =
SELECTEDMEASURE ()
- CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( Календарь[Дата] ) )
Рост к ПГ, % =
VAR _Cur = SELECTEDMEASURE ()
VAR _Ref = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( Календарь[Дата] ) )
RETURN DIVIDE ( _Cur - _Ref, _Ref )Теперь это две оси в матрице: «Период» (Текущий / YTD / …) и «Сравнение» (Значение / Δ абс / Рост %). На пересечении «YTD × Рост к ПГ, %» вы получите рост YTD к аналогичному YTD прошлого года — без отдельной меры под эту комбинацию.
Когда применяются две группы, их SELECTEDMEASURE() вкладываются друг в друга, а порядок вложения задаётся свойством Precedence: группа с бо́льшим precedence применяется первой (внутренней). Ставим «Период» выше «Сравнения» — тогда сначала к базовой мере применяется период (например, YTD), а «Сравнение» оборачивает уже преобразованную меру. Поэтому в пункте «Рост к ПГ, %» и SELECTEDMEASURE(), и его сдвиг на год считаются в YTD-контексте — выходит честный «YTD vs PY YTD». Одна и та же группа сравнения работает поверх любого периода.
Платёж за это — настоящий: P периодов × C способов сравнения комбинаций при P + C пунктах в сумме. Добавили новый период — он сразу получает все сравнения; добавили новое сравнение — оно сразу работает для всех периодов.
Сравнение с произвольным периодом (отвязанный календарь)
Прошлый год — частный случай. Бизнесу часто нужно сравнить текущие продажи с любым периодом: «как сейчас против прошлой акции», «против лучшего квартала». Жёстко в DAX такой период не зашьёшь — его должен выбирать пользователь. Приём: второй, отвязанный календарь.
Заводим таблицу КалендарьСравнения с теми же датами, но без связи с фактом (отдельный «остров данных», как disconnected-параметр из урока про what-if). Пользователь выбирает в её срезе диапазон-эталон. Срез не фильтрует продажи напрямую — мы читаем выбор через MIN/MAX и применяем его руками к настоящему Календарю:
-- пункты в группе «Сравнение»
Δ к выбранному, абс =
VAR _От = MIN ( КалендарьСравнения[Дата] )
VAR _До = MAX ( КалендарьСравнения[Дата] )
VAR _Cur = SELECTEDMEASURE ()
VAR _Ref =
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( Календарь ),
Календарь[Дата] >= _От && Календарь[Дата] <= _До
)
RETURN _Cur - _Ref
Рост к выбранному, % =
VAR _От = MIN ( КалендарьСравнения[Дата] )
VAR _До = MAX ( КалендарьСравнения[Дата] )
VAR _Cur = SELECTEDMEASURE ()
VAR _Ref =
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( Календарь ),
Календарь[Дата] >= _От && Календарь[Дата] <= _До
)
RETURN DIVIDE ( _Cur - _Ref, _Ref )Связанный календарь сразу бы отфильтровал продажи — и «эталон» совпал бы с текущим периодом. Отвязанная таблица этого не делает: её выбор живёт сам по себе, а мы достаём его границы (MIN/MAX) и строим эталонный контекст вручную. REMOVEFILTERS ( Календарь ) обязателен — он снимает фильтр текущего периода, иначе пересечение с выбранным диапазоном даст пусто. Так _Ref — это мера за произвольный, выбранный пользователем период.
Точно так же это composition с группой «Период»: при «YTD × Рост к выбранному, %» сравнение применится поверх YTD-контекста. А если выбор должен быть произвольным набором дат (не сплошным диапазоном), вместо >= … && <= … берут TREATAS ( VALUES ( КалендарьСравнения[Дата] ), Календарь[Дата] ) — перенос выбранных дат на реальный календарь (урок про виртуальные связи, TREATAS).
- Применяется ко всему. Пункт обернёт любую меру, попавшую в визуал, даже ту, где время бессмысленно (например, «средняя цена» или текстовая мера). При необходимости защищайтесь проверкой
ISSELECTEDMEASUREили возвратомSELECTEDMEASURE()без обёртки. - Несколько групп → precedence. Как только появляется вторая группа («Сравнение» выше, «Валюта» и т.п.), порядок их вложения задаётся свойством Precedence. Перепутаете — и «YTD × Рост %» посчитается не от того периода. Правило: группа с бо́льшим precedence применяется первой (внутренней).
- Нет таблицы дат — нет TI. Самая частая причина «пустого PY»: таблица дат не помечена или с дырами.
Что дальше
Весь time intelligence теперь живёт в одном наборе и применяется к любой мере. Дальше — выход за пределы одного файла: модели на SSAS / Analysis Services Tabular. Следующий урок.