Большинство мер аддитивны: выручку за год можно сложить из месяцев. Но есть показатели, которые так складывать нельзя, — остатки на складе, баланс счёта, число активных подписок. Это semi-additive меры, и для них нужен особый приём. Разбираем по DAX Patterns SQLBI.
Учебный набор — продажи (они аддитивны). Остатки — отдельный факт, поэтому примеры на гипотетической таблице Склад со столбцами Дата, Товар, Остаток. Логика универсальна для любых балансов.
Три типа мер
- Аддитивные — суммируются по всем измерениям, включая время: выручка, количество. Год = сумма месяцев.
- Semi-additive — суммируются по обычным измерениям, но не по времени: остаток, баланс. Остаток за год ≠ сумма остатков по месяцам.
- Неаддитивные — не суммируются вообще (например, отношения, проценты).
Остаток на 31 января (100 шт) и на 28 февраля (120 шт) сложить в «220 за два месяца» — бессмыслица. Остаток за период — это остаток на конец периода (или среднее), а не сумма. Поэтому обычный SUM по времени врёт.
Остаток на конец периода
Берём значение на последнюю дату периода, на которую есть данные:
Остаток на конец =
CALCULATE (
SUM ( Склад[Остаток] ),
LASTNONBLANKVALUE ( Календарь[Дата], CALCULATE ( SUM ( Склад[Остаток] ) ) )
)LASTNONBLANKVALUE находит последнюю дату в контексте, где остаток не пуст, и берёт значение на неё. В строке «Год» это будет остаток на конец года, в «Январь» — на конец января. По товарам и складам (обычные измерения) при этом всё корректно суммируется.
Проще, если данные есть на каждый день: LASTDATE:
Остаток на конец (LASTDATE) =
CALCULATE ( SUM ( Склад[Остаток] ), LASTDATE ( Календарь[Дата] ) )Средний остаток
Иногда нужен не конечный, а средний остаток за период — для оборачиваемости:
Средний остаток =
AVERAGEX ( VALUES ( Календарь[Дата] ), CALCULATE ( SUM ( Склад[Остаток] ) ) )Усредняем дневные остатки по дням периода.
Положить SUM(Склад[Остаток]) в матрицу с месяцами в строках и годом в итоге — и получить в «Итого» сумму всех дневных остатков (астрономическое число). Для остатков итог по времени — это LASTNONBLANKVALUE/LASTDATE или среднее, но не SUM.
Где встречается
Остатки склада, баланс счёта/кассы, дебиторка/кредиторка на дату, число активных абонентов, незавершённое производство. Везде, где показатель — это «снимок на момент», а не «накопление за период».
Что дальше
Semi-additive закрыли. Дальше — TopN с автоматическим сворачиванием хвоста в «Прочие». Следующий урок.