dax первый день квартала
Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
DAX функции ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH в Power BI и Power Pivot
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы обсудим группу 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 () — возвращает последнюю известную дату месяца в рамках текущего контекста.
DAX функции STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH в Power BI и Power Pivot
STARTOFYEAR () — возвращает первую известную дату года в рамках текущего контекста.
STARTOFQUARTER () — возвращает первую известную дату квартала в рамках текущего контекста.
STARTOFMONTH () — возвращает первую известную дату месяца в рамках текущего контекста.
Пример формул на основе функций группы ENDOF и STARTOF (YEAR, QUARTER и MONTH)
В модели данных Power BI Desktop имеется исходная таблица «Банковский Счет», в которой расположена информация о сальдо баланса банковского счета организации за каждый день с 15 января по 7 апреля 2018 года:
Сперва, для объяснения сути работы всех DAX функций ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH, рассмотрим простой пример их работы.
Создадим в Power BI Desktop во вкладке «Моделирование» в исходной таблице «Банковский счет» 2 временных вычисляемых столбца на основе следующих формул с использованием функций STARTOFMONTH и ENDOFMONTH:
Результатом выполнения этих формул, в исходной таблице у нас появились 2 вычисляемых столбца:
Из этих созданных столбцов мы можем увидеть, что для каждой даты в столбце [Дата] соответствует реально существующая дата начала или конца этого месяца, то есть, для 30 января — дата начала месяца 15 января (так как баланс счета у нас начинается с 15 января) и дата конца месяца — 31 января.
То же самое и для других дат — для 3 февраля, дата начала месяца 1 февраля, а дата конца месяца 28 февраля.
В этом и заключается вся суть DAX функций ENDOFYEAR, ENDOFQUARTER, ENDOFMONTH и STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH. Но, сами по себе эти функции весьма бесполезны и их нужно использовать совместно с другими функциями языка DAX.
Рассмотрим пример далее, с более сложными формулами.
Создадим в Power BI три меры, рассчитывающие значение сальдо баланса банковского счета организации на начало 3 периодов — на начало месяца, квартала и года. Код формул будет таким:
Во всех этих формулах мы используем специальную DAX функцию — CALCULATE (подробнее об этой функции Вы можете прочитать в этой статье). Эта функция позволяет нам вычислить сумму, рассчитанную функцией SUM, по столбцу [Баланс Счета], но не по всем строкам этого столбца, а только по одной единственной строке, которую возвратят каждая в своей формуле функции STARTOFMONTH, STARTOFQUARTER и STARTOFYEAR, находящиеся во втором параметре CALCULATE в качестве фильтров. А если быть точнее, то по строкам начала месяца, квартала и года в рамках текущего контекста.
В итоге, в отчетах Power BI, на основе выше приведенных формул, мы можем наблюдать следующую визуализацию:
Из этой визуализации мы уже можем наблюдать практическую ценность формул на основе DAX функций STARTOFYEAR, STARTOFQUARTER, STARTOFMONTH. То есть, из так называемой, таблицы ежедневных снимков баланса счета, мы получили точные значения сальдо баланса на начало месяца, квартала и года.
С функциями ENDOFYEAR, ENDOFQUARTER и ENDOFMONTH — та же самая история, только в их случае, сальдо баланса мы уже получим на конец периода (года, квартала, месяца).
На этом в этой статье, с функциями, возвращающими даты последнего / первого дня года, квартала или месяца в текущем контексте в Power BI и Power Pivot, все.
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи? 
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
DATESBETWEEN
Возвращает таблицу, содержащую столбец дат, который начинается с указанной даты начала и продолжается до указанной даты окончания.
Эта функция подходит для передачи в качестве фильтра функции CALCULATE. Используйте его для фильтрации выражения по настраиваемому диапазону дат.
Если вы работаете со стандартными интервалами дат, такими как дни, месяцы, кварталы или годы, рекомендуется использовать более подходящую функцию DATESINPERIOD.
Синтаксис
Параметры
| Термин | Определение |
|---|---|
| dates | Столбец даты. |
| start_date | Выражение даты. |
| end_date | Выражение даты. |
Возвращаемое значение
Таблица, содержащая один столбец значений даты.
Remarks
В наиболее распространенном варианте использования dates является ссылкой на столбец дат помеченной таблицы дат.
Если значение start_date равно BLANK, start_date будет самым ранним значением в столбце dates.
Если значение end_date равно BLANK, end_date будет самым поздним значением в столбце dates.
Даты, используемые в параметрах start_date и end_date, являются включающими. Например, если значение start_date равно 1 июля 2019 г, то эта дата будет включена в возвращаемую таблицу (предоставляя дату в столбце dates).
Возвращаемая таблица может содержать только даты, хранящиеся в столбце dates. Например, если столбец dates начинается с 1 июля 2017 г., а значение start_date равно 1 июля 2016 г., то возвращаемая таблица будет начинаться с 1 июля 2017 г.
Эта функция не поддерживается для использования в режиме DirectQuery при использовании в вычисляемых столбцах или правилах безопасности на уровне строк (RLS).
Пример
В следующем определении меры таблицы Sales используется функция DATESBETWEEN для создания вычисления срока действия (LTD). Срок действия представляет накопление меры с течением времени с момента самого начала времени.
Обратите внимание, что в формуле используется функция MAX. Эта функция возвращает последнюю дату в контексте фильтра. Таким образом, функция DATESBETWEEN возвращает таблицу дат, начиная с самой ранней даты до момента, когда сообщается о последней дате.
Примеры в этой статье можно добавить в образец модели Power BI Desktop. Чтобы получить модель, см. пример модели DAX.
Предположим, что самая ранняя дата, хранящаяся в таблице Date, — 1 июля 2017 г. Поэтому, когда отчет фильтрует меру по июню 2020 г., функция DATESBETWEEN возвращает диапазон дат с 1 июля 2017 г. по 30 июня 2020 г.
WEEKDAY
Возвращает число от 1 до 7, указывающее день недели для даты. По умолчанию дни отображаются в диапазоне от 1 (воскресенье) до 7 (суббота).
Синтаксис
Параметры
| Термин | Определение |
|---|---|
| дата | Дата в формате datetime. |
Даты должны вводиться с помощью функции DATE, с помощью выражений, которые приводят к дате, или в качестве результата других формул.
Тип возвращаемого значения: 1, неделя начинается с воскресенья (1) и заканчивается в субботу (7). Нумерация с 1 до 7.
Тип возвращаемого значения: 2, неделя начинается с понедельника (1) и заканчивается в воскресенье (7).
Тип возвращаемого значения: 3, неделя начинается с понедельника (0) и заканчивается в субботу (6). Нумерация с 1 до 7.
Возвращаемое значение
Целое число от 1 до 7.
Примечания
В отличие от Microsoft Excel, в котором значения даты хранятся в виде порядковых чисел, в DAX при работе со значениями даты и времени используется формат datetime. Если необходимо отобразить даты в виде серийных номеров, можно использовать параметры форматирования в Excel.
Можно также ввести даты в виде допустимого текстового представления даты, но во избежание непредвиденных результатов лучше преобразовать текстовую дату в формат datetime.
Если аргумент date является текстовым представлением даты, функция использует языковой стандарт и параметры даты и времени клиентского компьютера, чтобы понять текстовое значение и выполнить преобразование. Если в соответствии с текущими параметрами даты и времени даты представляются в формате «День.Месяц.Год», то строка «08.01.2009» будет интерпретироваться как значение типа datetime, эквивалентное 8 января 2009 года. Однако если в соответствии с текущими параметрами даты/времени даты представляются в формате «Месяц.День.Год», та же строка будет преобразована в значение типа datetime, эквивалентное 1 августа 2009 года.
Пример
В следующем примере показано получение даты из столбца [HireDate], добавление 1 и отображение дня недели, соответствующего этой дате. Поскольку аргумент return_type опущен, используется формат по умолчанию, в котором 1 — воскресенье, а 7 — суббота. Если результат равен 4, то день будет иметь значение «Среда».
Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
DAX функции YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы рассмотрим 3 функции, которые относятся к категории функций дат и времени в DAX — YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot.
Рассмотрим каждую функцию в отдельности.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.
DAX функция YEARFRAC в Power BI и PowerPivot
YEARFRAC () — вычисляет долю указанного периода дат в целом году.
Где, «Стартовая Дата» и «Конечная Дата» — даты в формате datetime, а «Базис» — способ вычисления дня (необязательный параметр).
Рассмотрим пример формулы на основе DAX функции YEARFRAC.
В данной формуле начальную и конечную даты мы задали при помощи DAX функции DATE. Период между этими двумя датами составляет ровно 1 месяц. В качестве базиса (способа вычисления дня) я взял европейский стандарт (4).
Как результат, формула на основе YEARFRAC вывела долю этого периода относительно целого года = 8.33 %:
У созданной меры в Power BI Desktop нужно поменять формат на % и вывести два знака после запятой.
DAX функция WEEKDAY в Power BI и PowerPivot
WEEKDAY () — возвращает день недели в формате чисел 1-7 (0-6). По умолчанию неделя начинается с воскресенья (1) и заканчивается субботой (7).
Примеры формул на основе DAX функции WEEKDAY.
В качестве параметров даты в WEEKDAY можно вставлять столбец со значениями даты. Давайте рассмотрим такой пример формулы.
В Power BI имеется исходная таблица с датами, где 1 января 2018 — понедельник:
Добавим в эту таблицу 2 вычисляемых столбца на основе следующих формул с участием DAX функции WEEKDAY:
То есть, в первой формуле начало недели начинается в воскресенье (1), а во второй формуле — в понедельник (1).
В итоге, результат будет таким:
В столбце на основе первой формулы 1 января (понедельник) равен числу 2, так как начало недели в воскресенье (1). А в столбце на основе второй формулы 1 января (понедельник) равен числу 1, так как начало недели, также, в понедельник (1).
DAX функция WEEKNUM в Power BI и PowerPivot
WEEKNUM () — возвращает номер недели года (относительно начала года).
Примеры формул на основе DAX функции WEEKNUM.
Как и в функции WEEKDAY, в качестве параметров даты в WEEKNUM, также можно вставить столбец со значениями даты. Давайте рассмотрим такой пример формулы.
В Power BI Desktop имеется исходная таблица с датами, где 1 апреля 2018 — это воскресенье:
Добавим в эту таблицу 2 вычисляемых столбца на основе следующих формул с участием DAX функции WEEKNUM:
Исходя из синтаксиса, в первой формуле начало недели начинается в воскресенье, а во второй формуле — в понедельник.
В итоге, результат будет таким:
Так как в столбце, рассчитанном на основе первой формулы, начало недели в воскресенье, а в таблице у нас представлены 3 дня — воскресенье (1 апреля), понедельник (2 апреля), вторник (3 апреля), то у этих всех 3-х дней один номер недели = 14.
В столбце, рассчитанном на основе второй формулы, начало недели в понедельник. И именно поэтому, 1 апреля (воскресенье) имеет номер недели 13, а у 2 и 3 апреля, номер недели уже 14.
На этом, с разбором функций YEARFRAC, WEEKDAY и WEEKNUM в Power BI и PowerPivot, в данной статье все.
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи? Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
Как в Power BI (Power Pivot) получить первую (последнюю) известную дату? DAX функции FIRSTDATE и LASTDATE
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. Этой статьей мы заканчиваем рассматривать функции в DAX из категории time intelligence (логики операций с датами и временем). А конкретно, мы разберем работу двух достаточно простых функций FIRSTDATE и LASTDATE, возвращающих первую (последнюю) известную дату в Power BI и Power Pivot.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.
DAX функции FIRSTDATE и LASTDATE в Power BI и Power Pivot
FIRSTDATE () — возвращает первую известную дату для указанного столбца дат в текущем контексте.
LASTDATE () — возвращает последнюю известную дату для указанного столбца дат в текущем контексте.
Иногда я встречаю, что пользователи языка DAX в Power BI или Power Pivot пишут данные функции в два раздельных слова: FIRST DATE или LAST DATE, что, естественно, неправильно.
Пример формул на основе DAX функций FIRSTDATE и LASTDATE
Разберем формулы с участием DAX функций FIRSTDATE и LASTDATE на основе примера продажи некого товара.
В Power BI имеется исходная таблица «Продажи товара», где содержится информация по дате сделки продажи:
Для тех или иных задач, для построения каких-то исследовательских отчетов в Power BI, на основании имеющихся данных по продажам, нам требуется получить даты первой и последней сделки по продаже товара. Как Вы понимаете, для этих целей нам подойдут рассматриваемые в этой статье time intelligence функции FIRSTDATE и LASTDATE, так как они, как раз таки, и возвратят нам эти даты первой и последней продажи.
Итак, составим соответствующие формулы мер в Power BI Desktop:
И в отчетах Power BI мы можем пронаблюдать работу формул этих мер:
Теперь, больше не для того, чтобы понять работу функций FIRSTDATE и LASTDATE (они и так простые и с ними все понятно), а для тренировки составления формул на языке DAX, рассмотрим пример с формулой, несколько сложнее предыдущих.
Итак, нам требуется составить такой код формулы, чтобы получить дату последней сделки, но только для тех продаж, которые были совершены до 25 января.
То есть, в нашу исходную прошлую формулу нужно вставить каким-то образом фильтр, который отфильтрует все даты продаж до 25 января. Код формулы будет таким:
Где, в качестве параметра столбца с датами в функции LASTDATE мы вставили функцию FILTER, которая и отфильтровывает даты сделок до 25 января следующим условием:
Для сравнения в условии даты сделок с 25 января мы воспользовались еще одной DAX функцией DATE, которая позволила в этом условии записать саму дату.
Также, пришлось использовать еще одну DAX функцию — ALL. Потому как, функция FILTER, если в ее первый параметр вставить просто таблицу «Продажи Товара», то она возвратит отфильтрованную таблицу, что не подойдет для функции LASTDATE, так как в параметре LASTDATE — должен быть столбец, а не таблица. И функция ALL, внутрь которой мы вставили ссылку на столбец [Дата Сделки], в результате возвратила столбец в виде таблицы. Что подошло как и для FILTER (так как в ней нужна ссылка именно на таблицу), так и для LASTDATE (так как для нее нужна ссылка на столбец).
Итог работы этой формулы мы можем наблюдать в отчетах Power BI:
Как мы видим из этой визуализации в Power BI, дата последней продажи у нас вывелась не 28 января, как в первом примере, а 23 января.
На этом в этой статье, с разбором DAX функций FIRSTDATE и LASTDATE, возвращающих первую (последнюю) известные даты в Power BI или PowerPivot, все.
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи? Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D