Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

• DAX функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER и CLOSINGBALANCEMONTH в Power BI и Power Pivot
• DAX функции OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER и OPENINGBALANCEMONTH в Power BI и Power Pivot
• Пример формул на основе функций группы CLOSINGBALANCE (YEAR, QUARTER и MONTH)

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы продолжаем изучать функции категории time intelligence (логики операций с датами и временем) в DAX, а конкретно, разберем функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, CLOSINGBALANCEMONTH и OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER, OPENINGBALANCEMONTH, которые позволяют вычислить выражение на последнюю / первую даты года, квартала и месяца в Power BI и Power Pivot.

Данные функции очень хорошо подходят для того, чтобы в отчетах Power BI рассчитать сальдо (по балансу), остатки (запасов, товаров) на конец или начало нужных периодов (год, квартал, месяц). Как еще говорят, сделать снимки какого-либо показателя или измерения (например, баланса счета) на определенную дату.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

Да, и еще один момент, в рамках распродажи до 29 ноября 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

В этом видеокурсе язык DAX преподнесен как простой конструктор, состоящий из нескольких блоков, которые имеют свое определенное, конкретное предназначение. Сочетая различными способами эти блоки, Вы, при помощи конструктора формул DAX, с легкостью сможете решать любые (простые или сложные) аналитические задачи.

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 29 ноября 2024 г.): узнать подробнее

до конца распродажи осталось:

 

DAX функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER и CLOSINGBALANCEMONTH в Power BI и Power Pivot

CLOSINGBALANCEYEAR () — вычисляет заданное выражение на последнюю дату года текущего контекста.
CLOSINGBALANCEQUARTER () — вычисляет заданное выражение на последнюю дату квартала текущего контекста.
CLOSINGBALANCEMONTH () — вычисляет заданное выражение на последнюю дату месяца текущего контекста.

Синтаксис:

CLOSINGBALANCEYEAR (Выражение; [Дата]; Фильтр; "Конец Года")
CLOSINGBALANCEQUARTER (Выражение; [Дата]; Фильтр)
CLOSINGBALANCEMONTH (Выражение; [Дата]; Фильтр)

Где:

• Выражение — выражение, возвращающее единственное скалярное значение
• [Дата] — столбец из дат или выражений, возвращающих даты
• Фильтр — (необязательный параметр) необходимые фильтры для вычисления выражения
• «Конец Года» — (необязательный параметр) текстовая дата, записанная в виде «01/06» («день/месяц»). Определяет дату окончания года (по умолчанию «31/12»)

 

DAX функции OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER и OPENINGBALANCEMONTH в Power BI и Power Pivot

OPENINGBALANCEYEAR () — вычисляет заданное выражение на первую дату года текущего контекста.
OPENINGBALANCEQUARTER () — вычисляет заданное выражение на первую дату квартала текущего контекста.
OPENINGBALANCEMONTH () — вычисляет заданное выражение на первую дату месяца текущего контекста.

Синтаксис:

OPENINGBALANCEYEAR (Выражение; [Дата]; Фильтр; "Конец Года")
OPENINGBALANCEQUARTER (Выражение; [Дата]; Фильтр)
OPENINGBALANCEMONTH (Выражение; [Дата]; Фильтр)

Где:

• Выражение — выражение, возвращающее единственное скалярное значение
• [Дата] — столбец из дат или выражений, возвращающих даты
• Фильтр — (необязательный параметр) необходимые фильтры для вычисления выражения
• «Конец Года» — (необязательный параметр) текстовая дата, записанная в виде «01/06» («день/месяц»). Определяет дату окончания года (по умолчанию «31/12»)

 

Пример формул на основе функций группы CLOSINGBALANCE (YEAR, QUARTER и MONTH)

Рассмотрим пример формул на основе DAX функций категории time intelligence CLOSINGBALANCEYEAR и CLOSINGBALANCEQUARTER.

В модели данных Power BI Desktop имеется исходная таблица «Сальдо счета», в которой на последнюю дату каждого месяца (с января 2018 по ноябрь 2018) прописан остаток баланса на счете организации. При чем, последняя дата баланса 20 ноября 2018 года, то есть, месяц еще не закончился и, соответственно, 4 квартал и год, также, еще не закончены:

 

Задача — создать отчет в Power BI, который бы показывал остаток (сальдо) средств на счете организации на конец каждого квартала и года, а также, на последнее текущее число (в данном примере, последнее текущее число — 20 ноября 2018).

Для этого, воспользуемся нашими DAX функциями, которые мы рассматриваем в этой статье — CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, так как они способны вычислить выражение на последнюю известную дату года и квартала. Составим на основе них соответствующие формулы расчета сальдо баланса счета на последние даты периодов (года и квартала):

Сальдо Год = 
CLOSINGBALANCEYEAR (
    SUM ('СальдоСчета'[БалансСчета]);
    'СальдоСчета'[Месяц]
)

Сальдо Квартал = 
CLOSINGBALANCEQUARTER (
    SUM ('СальдоСчета'[БалансСчета]);
    'СальдоСчета'[Месяц]
)

В первом параметре этих формул мы прописали само выражение, которое нам нужно вычислить — в данном случае, это сумма по столбцу [Баланс Счета], которую мы вычислили при помощи DAX функции SUM. Но, на самом деле, здесь сумма не высчитывается по всему столбцу, а только по одной его строчке — соответствующей последней известной дате квартала и года. И это ограничение на выражение накладывают уже сами функции CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER на основе тех дат, которые поданы во втором параметре этих функций.

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

В итоге, в Power BI мы можем наблюдать следующие визуализации сальдо баланса счета на конец квартала и года:

 

Теперь, рассмотрим еще один пример формулы на основе DAX функции CLOSINGBALANCEMONTH.

В модели данных Power BI имеется таблица «Товары», в которой отображены по определенным датам остатки товара на складе за февраль, март и начало апреля:

 

Задача — создать отчет в Power BI, показывающий в реальном времени текущие остатки товара за месяц, а также, остатки товара на конец прошлых месяцев.

По сути, формула на основе функции CLOSINGBALANCEMONTH языка DAX будет идентична тем формулам, которые мы рассматривали выше:

Остаток На Конец Месяца = 
CLOSINGBALANCEMONTH (
    SUM ('Товары'[ОстатокТовара]);
    'Товары'[Дата]
)

Результатом выполнения этой формулы на основе CLOSINGBALANCEMONTH, в Power BI Desktop будет следующая визуализация остатков товара на складе на конец месяца:

 

На этом, с разбором DAX функций CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER, CLOSINGBALANCEMONTH и OPENINGBALANCEYEAR, OPENINGBALANCEQUARTER, OPENINGBALANCEMONTH, вычисляющих выражение на последнюю / первую даты года, квартала, месяца в Power BI и Power Pivot, в этой статье все.

Переходите к следующей статье, где мы разберем похожие функции, возвращающие даты первого / последнего дня года, квартала и месяца.

---

---

 

Друзья, хотите изучить полный курс по языку DAX для Power BI и Power Pivot
со скидкой 50% вместо 10000 всего за 5000 р.?

 
акция действует до 29 ноября

 

 

 
 
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
 
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D