Как найти баланс на конец месяца

Со сводными таблицами 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) – на конец квартала, года.

Когда применять эти формулы. Если у вас есть данные об остатках, а в отчёте нужно показать итоги за период.
Формула для остатков на конец месяца:

Результат вычислений получается аналогичным выражению с ENDOFMONTH.

На что обратить внимание. На вычисления CLOSINGBALANCE влияет то, какой именно столбец с датами указан в DAX-формуле. Так, при вычислениях по кварталам при подстановке дат из справочника дат (а) или сразу из таблицы данных (б) получится два варианта итогов.

• Остатки на начало периода

Остатки на начало периода считаются так же просто, как в предыдущих примерах (но есть нюанс).
Вычисления можно сделать с помощью формул «открытия баланса» OPENINGBALANCE (MONTH, QUARTER, YEAR).
OPENINGBALANCEMONTH определяет значение на начало месяца, OPENINGBALANCEQUARTER – на начало квартала, OPENINGBALANCEYEAR – начало года.

Формула для входящих остатков на начало месяца:

На что обратить внимание. В примерах выше для OPENINGBALANCE и при «закрытии баланса» CLOSINGBALANCE, в таблицах с исходными данными должны быть указаны значения на конец периода, потому что все остальные даты в вычислениях игнорируются. Если данных на конец периода нет, то формула выдаст пустое значение.

Если в вашем сценарии расчета остатки на начало месяца берутся на 1-е число (а не «переходящие» остатки из предыдущего месяца), то вам больше подойдет формула STARTOFMONTH.

Способ 2. LASTNONBLANK и FIRSTNONBLANK

LASTNONBLANK – практически незаменимая формула для моделирования остатков. С её помощью можно найти последнее «непустое» значение. Более компактный вариант вычислений получается с формулой LASTNONBLANKVALUE.

Когда применять. Вычисления с LASTNONBLANK хорошо работают, когда нужно найти последнее «непустое» значение, например покупку клиента, коэффициент или индекс, который вступил в действие с указанной даты.
В случае с товарами такая логика подходит, когда остатки в таблице с исходными данными как бы «зафиксированы» до даты изменения. Нулевые остатки в этом случае явно обозначаются: как только остаток становится равным нулю, в таблице с исходными данными должен быть указан «ноль» ( =  0).

Для правильной работы выражения с LASTNONBLANK может потребоваться отдельная мера для остатков. Если такую меру не создать, то итоги могут отображаться некорректно.

Формула остатков:

Значения на начало месяца легко определить с помощью с FIRSTNONBLANK или FIRSTNONBLANKVALUE.

На что обратить внимание. Аналитики, которые впервые создают отчеты с формулой LASTNONBLANK, могут столкнуться с некорректным расчетом итогов. Ниже примеры двух вариантов формулы LASTNONBLANK – с мерой и без меры. И результаты, в которых немного отличаются итоговые столбцы.

Во втором варианте (б) видно, что в итоговый столбец попали значения только на завершающую дату. В первой таблице (а) приведён корректный вариант вычислений, который получается при использовании LASTNONBLANKVALUE. Тот же результат будет, если в LASTNONBLANK выражение SUM('данные'[остатки]) обернуть формулой CALCULATE и записать строку в таком виде: CALCULATE( SUM( 'данные'[остатки]) )

На что ещё обратить внимание. Ещё одна особенность вычислений с LASTNONBLANK состоит в том, что в общий итог таблицы также попадает последнее итоговое непустое значение (см. рисунок ниже). Если такой сценарий у вас не предусмотрен, воспользуйтесь формулой SUMX для суммирования по строкам.

Способ 3. TOTALYTD и формулы нарастающих итогов

Расчет остатков: входящие остатки + поступления — выбытия

В предыдущих случаях мы брали остатки сразу из таблицы данных. А что делать, если их нужно посчитать по формуле? Например, у нас есть данные на начало периода, поступление и расход. Тогда остатки на конец периода можно найти по формуле:

Остаток на конец = Остаток на начало + Поступление – Расход

Иногда в dashboard-ах такое моделирование делают с помощью сложных выражений. Но есть вариант проще – с помощью формул нарастающих итогов TOTALYTD (MTD, QTD). Более детально про эти формулы вы можете прочесть в статье «Нарастающие итоги с помощью DAX».

На что обратить внимание. Формулы TOTALYTD (MTD, QTD) хорошо работают с датами внутри года (или месяца, квартала). Эти формулы подходят для сценариев, когда входной остаток задается один раз в начале периода, например в начале года. Если расчёт делается для нескольких лет, то применяются формулы нарастающих итогов с CALCULATE. Такие вычисления можно настроить, например, с помощью быстрых мер:

Остатки годы =
CALCULATE (
[Остатки на начало] + [Поступление] - [Расход],
FILTER (
ALLSELECTED ( ‘Календарь'[Date] ),
ISONORAFTER ( ‘Календарь'[Date], MAX ( ‘Календарь'[Date] ), DESC ) )
)

Способ 4. Формулы с LASTNONBLANK

Стоимость при изменении цен, индексы, курсы валют

Особенность формулы LASTNONBLANK – брать последнее значение без суммирования, можно использовать для решения еще одной проблемы, которая возникает у аналитиков. А именно – для «протягивания» данных.

Что имеется в виду: представьте себе, что в одной таблице у вас курсы валют, а в другой – продажи. Эти таблицы связаны по дате через Календарь. И вам нужно по дням перемножить курс на цены. Вроде ничего сложного, но есть нюанс: курсы валют есть не на каждый день. И вам нужно в дни, когда нет курса валют «протянуть» данные из предыдущего дня (почти как команда «Заполнить вниз» в Power Query, но только в разных таблицах и с помощью DAX).

Для многих аналитиков это сложная задача, на которую они могут потратить много времени и сил. А мы разберём формулу, которая помогает при работе с такими данными:

• курсы валют с датами изменений,
• цены с датой вступления в силу,
• скидки по акциям с датами начала акций,
• индексы и коэффициенты, которые вступают в действие с определенного момента.

В общем, это такие сценарии, где у вас есть дата старта действия какого-то показателя. И этот показатель нужно сопоставить с другими данными в последующие дни.

Давайте разберем, как это всё работает. Например, у нас есть две таблицы:
1) запасы товаров в штуках с датами закупки;
2) цены, которые несколько раз менялись за период.
Нужно найти стоимость запасов по формуле: количество х цена.

Как видите, с помощью CALCULATE и LASTNONBLANK можно настраивать более сложные способы и сценарии моделирования остатков и не только. Так в модель можно добавить не только цены, но и индексы, коэффициенты и так далее. Например, с TOTALYTD определяем количество, а LASTNONBLANK добавляет в расчёт цену и курс валют.

Резюме

Сводные таблицы Excel и визуализации Power BI – это мощный функционал для анализа и представления данных. И эти инструменты хочется использовать не только для построения отдельных аналитических выкладок и презентаций, но и в подготовке вполне стандартных отчетов. И если отчеты по оборотам, где требуются суммы по полям данных, готовятся быстро и просто, то отчеты по остаткам требуют некоторых знаний DAX-формул.

Но, согласитесь, не так и много этих формул: CLOSING / OPENINGBALANCE, формулы с поиском значений по критерию «NONBLANK» и их сочетания с другими функциями. Зато какие возможности. Теперь вы можете с помощью сводных таблиц не только построить баланс, но и корректно рассчитать финансовые коэффициенты, не переживая, что остатки по месяцам просуммируются на конец года 🙂