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