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


Антон БудуевПриветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы обсудим группу DAX функций, входящих в категорию time intelligence (логики операций с датами и временем): ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH, возвращающих даты последнего / первого дня года, квартала или месяца в текущем контексте в Power BI и Power Pivot.

Эти рассматриваемые функции группы ENDOF и STARTOF (YEAR, QUARTER, MONTH) по своей сути несколько напоминают другие функции в языке DAX, вычисляющие нужное выражение в первый или последний день года, квартала или месяца. Ознакомится с ними Вы можете в статье по этой ссылке.

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

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

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

 

DAX функции ENDOFYEAR, ENDOFQUARTER и ENDOFMONTH в Power BI и Power Pivot

ENDOFYEAR () — возвращает последнюю известную дату года в рамках текущего контекста.
ENDOFQUARTER () — возвращает последнюю известную дату квартала в рамках текущего контекста.
ENDOFMONTH () — возвращает последнюю известную дату месяца в рамках текущего контекста.

Синтаксис:

ENDOFYEAR ([Дата]; "Конец Года")
ENDOFQUARTER ([Дата])
ENDOFMONTH ([Дата])

Где:

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

 

DAX функции STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH в Power BI и Power Pivot

STARTOFYEAR () — возвращает первую известную дату года в рамках текущего контекста.
STARTOFQUARTER () — возвращает первую известную дату квартала в рамках текущего контекста.
STARTOFMONTH () — возвращает первую известную дату месяца в рамках текущего контекста.

Синтаксис:

STARTOFYEAR ([Дата]; "Конец Года")
STARTOFQUARTER ([Дата])
STARTOFMONTH ([Дата])

Где:

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

 

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

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

Исходная таблица Банковский Счет

 

Сперва, для объяснения сути работы всех DAX функций ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH, рассмотрим простой пример их работы.

Создадим в Power BI Desktop во вкладке «Моделирование» в исходной таблице «Банковский счет» 2 временных вычисляемых столбца на основе следующих формул с использованием функций STARTOFMONTH и ENDOFMONTH:

Дата Начала Месяца = STARTOFMONTH ('БанковскийСчет'[Дата])
Дата Конца Месяца = ENDOFMONTH ('БанковскийСчет'[Дата])

Результатом выполнения этих формул, в исходной таблице у нас появились 2 вычисляемых столбца:

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

 

Из этих созданных столбцов мы можем увидеть, что для каждой даты в столбце [Дата] соответствует реально существующая дата начала или конца этого месяца, то есть, для 30 января — дата начала месяца 15 января (так как баланс счета у нас начинается с 15 января) и дата конца месяца — 31 января.

То же самое и для других дат — для 3 февраля, дата начала месяца 1 февраля, а дата конца месяца 28 февраля.

В этом и заключается вся суть DAX функций ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH. Но, сами по себе эти функции весьма бесполезны и их нужно использовать совместно с другими функциями языка DAX.

Рассмотрим пример далее, с более сложными формулами.

Создадим в Power BI три меры, рассчитывающие значение сальдо баланса банковского счета организации на начало 3 периодов — на начало месяца, квартала и года. Код формул будет таким:

БалансНаНачалоМесяца = 
CALCULATE (
    SUM ('БанковскийСчет'[БалансСчета]);
    STARTOFMONTH ('БанковскийСчет'[Дата])
)

БалансНаНачалоКвартала = 
CALCULATE (
    SUM ('БанковскийСчет'[БалансСчета]);
    STARTOFQUARTER ('БанковскийСчет'[Дата])
)

БалансНаНачалоГода = 
CALCULATE (
    SUM ('БанковскийСчет'[БалансСчета]);
    STARTOFYEAR ('БанковскийСчет'[Дата])
)

Во всех этих формулах мы используем специальную DAX функцию — CALCULATE (подробнее об этой функции Вы можете прочитать в этой статье). Эта функция позволяет нам вычислить сумму, рассчитанную функцией SUM, по столбцу [Баланс Счета], но не по всем строкам этого столбца, а только по одной единственной строке, которую возвратят каждая в своей формуле функции STARTOFMONTH, STARTOFQUARTER и STARTOFYEAR, находящиеся во втором параметре CALCULATE в качестве фильтров. А если быть точнее, то по строкам начала месяца, квартала и года в рамках текущего контекста.

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

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

 

Из этой визуализации мы уже можем наблюдать практическую ценность формул на основе DAX функций STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH. То есть, из так называемой, таблицы ежедневных снимков баланса счета, мы получили точные значения сальдо баланса на начало месяца, квартала и года.

С функциями ENDOFYEAR, ENDOFQUARTER и ENDOFMONTH — та же самая история, только в их случае, сальдо баланса мы уже получим на конец периода (года, квартала, месяца).

На этом в этой статье, с функциями, возвращающими даты последнего / первого дня года, квартала или месяца в текущем контексте в Power BI и Power Pivot, все.

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

 

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

 

 

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

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

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

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

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

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

 

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


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


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

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

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