Со сводными таблицами Excel и диаграммами Power BI легко работать, потому что в них по умолчанию настроено суммирование по полям: перетащил и готово. Если требуется не сумма, а, например, среднее значение или максимум — тоже не проблема.
Достаточно щелкнуть по полю правой кнопкой мыши и выбрать нужное вычисление. Другое дело, если вам в сводной таблице нужно вывести остатки. Представьте себе: вы добавляете в сводную таблицу остатки дебиторской задолженности по месяцам. Что должно быть в итоге за год? Правильно, значение на 31 декабря. А что выдаст сводная таблица? Сумму остатков по месяцам! И в данном случае автосуммирование уже мешает. Я встречал «решение» – отключить итоги. Согласитесь, это не вариант.
А ведь выводить остатки требуется во многих отчетах:
- запасы товаров на складах;
- дебиторская задолженность покупателей;
- ДДС (CF) – движение денежных средств;
- задолженность перед поставщиками и бюджетом;
- численность персонала компании;
- сальдо баланса.
И хорошо бы иметь возможность все эти отчеты строить с помощью сводных таблиц Excel и визуализировать в Power BI. Вопрос – можно ли это сделать, если там данные только суммируются? Да, можно, если пользоваться специальными DAX-формулами, доступными в Power Pivot для Excel и в Power BI.
В этой статье разберём, какие DAX-формулы и в каких случаях выбирать. Выбор подходящей формулы зависит от того, где вы берёте остатки: они где-то есть или вы их считаете сами. А также от поведения самих остатков — нужно чтобы они отображались на последний день периода или «переносились» на следующие даты. Отсюда способы расчёта остатков.
- Данные по остаткам у вас уже есть и вам их просто нужно добавить в отчет:
Способ 1. CLOSINGBALANCE и OPENINGBALANCE.
Способ 2. LASTNONBLANK и FIRSTNONBLANK.
- Остатки нужно рассчитать по формуле: входящие остатки + поступления – выбытия:
Способ 3. TOTALYTD и формулы нарастающих итогов.
- Подстановка остатков по дням и «перенос» внутри периодов:
Способ 4. Формулы с LASTNONBLANK.
Начнём с ситуаций, когда информация об остатках у вас уже есть, например, в выгрузке из учетной системы. И в зависимости от того, как выглядят данные, вы можете выбрать разные способы вычислений.
Способ 1. CLOSINGBALANCE и OPENINGBALANCE
Остатки на конец периода
Группа формул CLOSINGBALANCE (MONTH, QUARTER, YEAR) – формулы «закрытия баланса». Они точно пригодятся в отчёте по остаткам. CLOSINGBALANCEMONTH определяет значение на конец месяца, CLOSINGBALANCEQUARTER (YEAR) – на конец квартала, года.
Когда применять эти формулы. Если у вас есть данные об остатках, а в отчёте нужно показать итоги за период.
Формула для остатков на конец месяца:
| сальдо = CLOSINGBALANCEMONTH ( SUM ( 'данные'[остатки] ),'Календарь'[Date]) |
Результат вычислений получается аналогичным выражению с ENDOFMONTH.
| сальдо = CALCULATE ( SUM ( 'данные'[остатки] ),ENDOFMONTH ( 'Календарь'[Date] )) |
Формула ENDOFMONTH (QUARTER, YEAR) находит дату с последним днём месяца, квартала, года.
На что обратить внимание. На вычисления CLOSINGBALANCE влияет то, какой именно столбец с датами указан в DAX-формуле. Так, при вычислениях по кварталам при подстановке дат из справочника дат (а) или сразу из таблицы данных (б) получится два варианта итогов.
а) даты из справочника дат
| сальдо = CLOSINGBALANCEQUARTER ( SUM ( 'данные'[остатки] ),'Календарь'[Date]) |
Если подставить в формулу даты из Календаря, то итоги квартала будут дублироваться в каждом месяце. Если взять даты из таблицы с данными, получится более компактное представление с одним месяцем в квартале.
б) даты из таблицы данных
| сальдо = CLOSINGBALANCEQUARTER ( SUM ( 'данные'[остатки] ),'данные'[дата]) |
- Остатки на начало периода
Остатки на начало периода считаются так же просто, как в предыдущих примерах (но есть нюанс).
Вычисления можно сделать с помощью формул «открытия баланса» OPENINGBALANCE (MONTH, QUARTER, YEAR).
OPENINGBALANCEMONTH определяет значение на начало месяца, OPENINGBALANCEQUARTER – на начало квартала, OPENINGBALANCEYEAR – начало года.
Формула для входящих остатков на начало месяца:
| сальдо = OPENINGBALANCEMONTH ( SUM ( 'данные'[остатки] ),'Календарь'[Date]) |
В вычислениях с OPENINGBALANCE есть небольшой нюанс: результатом будут «переходящие» остатки, то есть цифры на конец предыдущего месяца.
Данные на 1-е число не принимаются во внимание: на рисунке видно, что в июне остатки на 31 мая.
На что обратить внимание. В примерах выше для OPENINGBALANCE и при «закрытии баланса» CLOSINGBALANCE, в таблицах с исходными данными должны быть указаны значения на конец периода, потому что все остальные даты в вычислениях игнорируются. Если данных на конец периода нет, то формула выдаст пустое значение.
Если в вашем сценарии расчета остатки на начало месяца берутся на 1-е число (а не «переходящие» остатки из предыдущего месяца), то вам больше подойдет формула STARTOFMONTH.
| сальдо = CALCULATE ( SUM ( 'данные'[остатки] ),STARTOFMONTH ( 'Календарь'[Date] )) |
STARTOFMONTH определяет дату начала месяца.
В отличие от примера c OPENINGBALANCE, в итоговую таблицу попадают данные на 1-е число.
Способ 2. LASTNONBLANK и FIRSTNONBLANK
LASTNONBLANK – практически незаменимая формула для моделирования остатков. С её помощью можно найти последнее «непустое» значение. Более компактный вариант вычислений получается с формулой LASTNONBLANKVALUE.
Когда применять. Вычисления с LASTNONBLANK хорошо работают, когда нужно найти последнее «непустое» значение, например покупку клиента, коэффициент или индекс, который вступил в действие с указанной даты.
В случае с товарами такая логика подходит, когда остатки в таблице с исходными данными как бы «зафиксированы» до даты изменения. Нулевые остатки в этом случае явно обозначаются: как только остаток становится равным нулю, в таблице с исходными данными должен быть указан «ноль» ( = 0).
Для правильной работы выражения с LASTNONBLANK может потребоваться отдельная мера для остатков. Если такую меру не создать, то итоги могут отображаться некорректно.
Формула остатков:
Остатки_мера = SUM ( 'данные'[остатки] )
Сальдо = |
| или более компактно:
Сальдо = |
Значения на начало месяца легко определить с помощью с FIRSTNONBLANK или FIRSTNONBLANKVALUE.
| Сальдо = FIRSTNONBLANKVALUE ( 'Календарь'[Date],SUM ( 'данные'[остатки] )) |
На что обратить внимание. Аналитики, которые впервые создают отчеты с формулой LASTNONBLANK, могут столкнуться с некорректным расчетом итогов. Ниже примеры двух вариантов формулы LASTNONBLANK – с мерой и без меры. И результаты, в которых немного отличаются итоговые столбцы.
а) Вычисления с мерой
| Сальдо = CALCULATE ( [Остатки_мера], LASTNONBLANK ( 'Календарь'[Date],[Остатки_мера] ) ) |
Все последние непустые значения:
б) Вычисления без меры
| Сальдо = CALCULATE ( SUM ( 'данные'[остатки] ),LASTNONBLANK ( 'Календарь'[Date],SUM ( 'данные'[остатки] ) )) |
Непустые значения за последний день:
Во втором варианте (б) видно, что в итоговый столбец попали значения только на завершающую дату. В первой таблице (а) приведён корректный вариант вычислений, который получается при использовании LASTNONBLANKVALUE. Тот же результат будет, если в LASTNONBLANK выражение SUM('данные'[остатки]) обернуть формулой CALCULATE и записать строку в таком виде: CALCULATE( SUM( 'данные'[остатки]) )
На что ещё обратить внимание. Ещё одна особенность вычислений с LASTNONBLANK состоит в том, что в общий итог таблицы также попадает последнее итоговое непустое значение (см. рисунок ниже). Если такой сценарий у вас не предусмотрен, воспользуйтесь формулой SUMX для суммирования по строкам.
Способ 3. TOTALYTD и формулы нарастающих итогов
Расчет остатков: входящие остатки + поступления — выбытия
В предыдущих случаях мы брали остатки сразу из таблицы данных. А что делать, если их нужно посчитать по формуле? Например, у нас есть данные на начало периода, поступление и расход. Тогда остатки на конец периода можно найти по формуле:
| Остаток на конец = Остаток на начало + Поступление – Расход |
Иногда в dashboard-ах такое моделирование делают с помощью сложных выражений. Но есть вариант проще – с помощью формул нарастающих итогов TOTALYTD (MTD, QTD). Более детально про эти формулы вы можете прочесть в статье «Нарастающие итоги с помощью DAX».
| Остатки к.п. = TOTALYTD ( [Остатки на начало] + [Поступление] - [Расход],'Календарь'[Date]) |
На что обратить внимание. Формулы TOTALYTD (MTD, QTD) хорошо работают с датами внутри года (или месяца, квартала). Эти формулы подходят для сценариев, когда входной остаток задается один раз в начале периода, например в начале года. Если расчёт делается для нескольких лет, то применяются формулы нарастающих итогов с CALCULATE. Такие вычисления можно настроить, например, с помощью быстрых мер:
| Остатки годы = CALCULATE ( [Остатки на начало] + [Поступление] - [Расход],FILTER ( ALLSELECTED ( ‘Календарь'[Date] ), ISONORAFTER ( ‘Календарь'[Date], MAX ( ‘Календарь'[Date] ), DESC ) ) ) |
Способ 4. Формулы с LASTNONBLANK
Стоимость при изменении цен, индексы, курсы валют
Особенность формулы LASTNONBLANK – брать последнее значение без суммирования, можно использовать для решения еще одной проблемы, которая возникает у аналитиков. А именно – для «протягивания» данных.
Что имеется в виду: представьте себе, что в одной таблице у вас курсы валют, а в другой – продажи. Эти таблицы связаны по дате через Календарь. И вам нужно по дням перемножить курс на цены. Вроде ничего сложного, но есть нюанс: курсы валют есть не на каждый день. И вам нужно в дни, когда нет курса валют «протянуть» данные из предыдущего дня (почти как команда «Заполнить вниз» в Power Query, но только в разных таблицах и с помощью DAX).
Для многих аналитиков это сложная задача, на которую они могут потратить много времени и сил. А мы разберём формулу, которая помогает при работе с такими данными:
- курсы валют с датами изменений,
- цены с датой вступления в силу,
- скидки по акциям с датами начала акций,
- индексы и коэффициенты, которые вступают в действие с определенного момента.
В общем, это такие сценарии, где у вас есть дата старта действия какого-то показателя. И этот показатель нужно сопоставить с другими данными в последующие дни.
Давайте разберем, как это всё работает. Например, у нас есть две таблицы:
1) запасы товаров в штуках с датами закупки;
2) цены, которые несколько раз менялись за период.
Нужно найти стоимость запасов по формуле: количество х цена.
1) запасы в штуках
2) цены
изменение цен по дням:
После объединения таблиц в модели данных (не путайте с Power Query) получилось, что даты в первой и второй таблице не совпадают. С помощью простого умножения стоимость посчитать не получается. И если в обычном Excel можно было бы просто вручную проставить ссылки на ячейки, то DAX так не работает.
Что же делать? Мы знаем, что в таблице с ценами указана дата старта действия цены. То есть нужно каким-то способом «перенести» цену на следующую дату. И вот для этого как раз пригодится формула LASTNONBLANK.
Формула для цен с учётом даты старта действия:
| Цена утвержденная = CALCULATE ( LASTNONBLANKVALUE ( 'Календарь'[Date],SUM ( 'цены'[Цена] )), 'Календарь'[Date] <= MAX ( 'Календарь'[Date] )) |
В результате получится таблица с ценами по всем датам периода.
Посчитать стоимость запасов поможет формула SUMX (подробнее про неё можно прочитать в этой статье).
Кстати, можно настроить компактной вид таблицы без перечисления всех дат. Для этого пригодится выражение IF + NOT ISEMPTY
| Цена = IF ( NOT ISEMPTY ( ‘данные_количество’ ), [Цена утвержденная] ) |
Как видите, с помощью CALCULATE и LASTNONBLANK можно настраивать более сложные способы и сценарии моделирования остатков и не только. Так в модель можно добавить не только цены, но и индексы, коэффициенты и так далее. Например, с TOTALYTD определяем количество, а LASTNONBLANK добавляет в расчёт цену и курс валют.
Резюме
Сводные таблицы Excel и визуализации Power BI – это мощный функционал для анализа и представления данных. И эти инструменты хочется использовать не только для построения отдельных аналитических выкладок и презентаций, но и в подготовке вполне стандартных отчетов. И если отчеты по оборотам, где требуются суммы по полям данных, готовятся быстро и просто, то отчеты по остаткам требуют некоторых знаний DAX-формул.
Но, согласитесь, не так и много этих формул: CLOSING / OPENINGBALANCE, формулы с поиском значений по критерию «NONBLANK» и их сочетания с другими функциями. Зато какие возможности. Теперь вы можете с помощью сводных таблиц не только построить баланс, но и корректно рассчитать финансовые коэффициенты, не переживая, что остатки по месяцам просуммируются на конец года 🙂
