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


Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы продолжаем изучать функции категории 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».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

 

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 мы можем наблюдать следующие визуализации сальдо баланса счета на конец квартала и года:

Результат работы формул в Power BI на основе DAX функций CLOSINGBALANCEYEAR, CLOSINGBALANCEQUARTER

 

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

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

Исходная таблица в модели данных Power BI - Товары

 

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

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

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

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

Результат работы формулы в Power BI на основе DAX функции CLOSINGBALANCEMONTH

 

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

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

Успехов Вам, друзья.

 

Антон БудуевС уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Присоединяйтесь к нашим социальным сетям

Именно в них оперативно и каждый день Вам будут доступны наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи 

по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel...)

Наша группа Вконтакте Мы в Инстаграме Наша группа в Фейсбук Наш YouTube канал
Наша группа VK
Подписаться на наш YouTube канал

 

 

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

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

 

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


Вам это может быть интересно. Другие статьи нашего обучающего портала:


Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

* Заполняя форму отправки комментария своими персональными данными (имя, email, сайт и др.), Вы автоматически подтверждаете свое согласие на обработку своих персональных данных