У нас накопились ответы на вопросы о накопительных итогах (даже ответы про накопительные итоги – накапливаются =) Такое впечатление, что с задачей рассчитать нарастающие или накопительные итоги сталкивается практически каждый слушатель наших курсов.
И вопрос стоит даже не в том, какую формулу использовать. Обычно всех интересуют нюансы. Например, как «остановить» нарастающий итог, чтобы он не отображался в периодах, где данных еще нет. Или как считать такой итог не в рамках года, а за все время работы компании или жизни проекта.
Немного о нарастающих итогах
Нарастающий итог – это сумма показателей, где к данным текущего периода добавляются суммы предыдущих периодов.
Вычисления нарастающих итогов обычно просят руководители, чтобы увидеть показатели с начала месяца, квартала или года, например, продажи или прибыль. Или посмотреть, сколько денег принес проект за все время работы. Совсем специфический случай – моделирование остатков (детали можно посмотреть в статье про формулы остатков).
Отсюда вычисления нарастающих итогов можно разделить на два вида:
а) внутри периода – с начала месяца, квартала, года;
б) без привязки к периодам.
В Power Pivot и Power BI для таких расчетов есть специальные формулы.
DAX-формулы для расчета нарастающих итогов
1. Нарастающие итоги с начала года
Нарастающие итоги с начала года считаются с помощью формул TOTALYTD или DATESYTD.
YTD = TOTALYTD ( [Факт], 'Календарь'[Дата] )
или
YTD = CALCULATE ( [Факт], DATESYTD ( 'Календарь'[Дата] ) )
2. Нарастающий итог с начала квартала
Нарастающий итог с начала квартала – формулы TOTALQTD или DATESQTD.
QTD = TOTALQTD ( [Факт], 'Календарь'[Дата] )
или
QTD = CALCULATE ( [Факт], DATESQTD ( 'Календарь'[Дата] ) )
3. Нарастающий итог с начала месяца
C начала месяца – формулы TOTALMTD или DATESMTD.
MTD = TOTALMTD ( [Факт], 'Календарь'[Дата] )
или
MTD = CALCULATE ( [Факт], DATESMTD ( 'Календарь'[Дата] ) )
4. Нарастающий итог без привязки к периодам
При расчете нарастающего итога без привязки к периодам показатели будут суммироваться с самого начала проекта – с его первой даты, а в начале нового периода не «сбросятся».
Нарастающий итог с начала проекта =
VAR _MaxDate = MAX ( 'Календарь'[Дата] )
RETURN
CALCULATE (
[Факт],
'Календарь'[Дата] <= _MaxDate,
ALL ( 'Календарь' )
)
Быстрые меры для нарастающих итогов
Кстати, для того чтобы посчитать нарастающие итоги, в Power BI вовсе не обязательно писать меры вручную. Их можно настроить с помощью «быстрых мер».
Добавить такую меру можно в меню Моделирование или Средства работы с таблицами (мерами) → Быстрая мера. Эта кнопка также доступна в выпадающем меню при нажатии на поле правой кнопкой мышки – Новая быстрая мера.
В быстрых мерах есть несколько видов нарастающих итогов — с начала периода (блок «Логика операций со временем») и от начала проекта (раздел «Итоги»)
Нарастающий итог в будущих периодах
Вот мы и подошли к нюансу оформления, который иногда сильно портит вид отчета. Особенно некрасивыми такие вычисления выглядят в таблицах. Потому что непонятно, почему в этих еще «не наступивших» периодах вообще есть какие-то цифры.
На рисунке ниже у нас есть данные только за январь-май. Но к значениям с июня по декабрь формула «прибавила» сумму, накопленную за первые месяцы.
Как вам такие график и таблица?
Ограничить отображение «ненаступивших» периодов можно двумя способами:
- Ограничение с помощью вычисляемого столбца в таблице дат.
- Ограничение дат только мерой.
Первый способ более простой по написанию формул и более эффективный с точки зрения производительности. Второй – используется, когда первый способ не подходит, например, у вас модель данных загружена в режиме Direct Query и вы не можете создавать столбцы.
1. Ограничение с помощью вычисляемого столбца в таблице дат
Создаем в справочнике дат – Календаре вычисляемый столбец:
ДатыФакт = 'Календарь'[Дата] <= MAX( 'Данные'[Дата] )
А далее записываем меру с нарастающим итогом, но в отличие от предыдущего примера к функции CALCULATE добавляем CALCULATETABLE:
Факт YDT =
CALCULATE (
[Факт],
CALCULATETABLE (
DATESYTD ( 'Календарь'[Дата] ),
'Календарь'[ДатыФакт] = TRUE
)
)
2. Ограничение дат только мерой
Если у вас нет возможности добавить вычисляемый столбец в таблицу дат, например, у вас модель данных загружена в режиме Direct Query и вы не можете создавать столбцы. Тогда используйте меру, которая найдет максимальную дату в таблице данных (это менее производительный способ). Например:
Факт YTD =
VAR _MaxDate = CALCULATE ( MAX ( 'Данные'[Дата] ), ALL ( 'Данные' ) )
VAR _DateFilter =
FILTER ( VALUES ( 'Календарь'[Дата] ), 'Календарь'[Дата] <= _MaxDate )
RETURN
CALCULATE (
[Факт],
DATESYTD ( _DateFilter )
)
Нарастающий итог за предыдущий год
Как правило нарастающие итоги текущего года сравнивают с другими показателями. Например, с нарастающим итогом за предыдущий год:
1.Находим показатели за прошлый год с формулой DATEADD.
Прошлый год = CALCULATE ( [Факт], DATEADD ( 'Календарь'[Дата], -1, YEAR ) )
2. Нарастающий итог за прошлый год (здесь важен порядок вычислений):
Прошлый год YTD = TOTALYTD ( [Прошлый год], 'Календарь'[Дата] )
Готово! В таблице видно, что нарастающий итог рассчитан за прошлый год.
Но посчитать итоговые отклонения пока не получится – предыдущий период нельзя сравнивать с неполным текущим годом.
Сопоставимые данные за текущий и предыдущий год
Чтобы привести к сопоставимому виду текущий и прошлый годы также требуется ограничение дат. Вот пример, как это сделать с использованием дополнительного столбца в справочнике дат и с функцией SAMEPERIODLASTYEAR.
Прошлый год YTD сопоставимый =
TOTALYTD (
[Факт],
CALCULATETABLE (
SAMEPERIODLASTYEAR ( 'Календарь'[Дата] ),
'Календарь'[ДатыФакт] = TRUE
)
)
В итоге у нас получается сопоставимый ряд данных, с помощью которого можно корректно посчитать отклонения.
