power bi последний день месяца
Как показать на диаграмме Power BI незавершенный период
Сегодня обсуждаем сравнение «несопоставимых» данных. А именно ситуации, когда в Power BI нужно показать информацию по годам или месяцам, но при этом текущий месяц или год еще не закончился. Или по нему введены не все данные. Если такой «незавершенный» период сравнивать с предыдущими, может показаться, что дела стали хуже. Отсюда будет сделан некорректный вывод. Чтобы этого избежать, нужно проинформировать пользователя о том, что данные не полные. Например, выделить незавершенный период другим цветом.
На диаграмме слева как раз показана ситуация, о которой мы говорим. Имеются данные о туристических поездках в России. Сейчас диаграмма явно указывает на спад туристических поездок в 2019 году. Оно едва превышает 2015 и 2016 годы.
А теперь смотрим на следующую диаграмму.
На самом деле в 2019 году просто не хватает данных, в нашем случае не хватает 4-го квартала. Без него текущий год с предыдущими сравнивать некорректно. Если 4-й квартал «как-бы» добавить, то увидим, что 2019 год превышает не только 2015 и 2016 годы, и, пожалуй, будет самым большим.
Но мы брали данные из открытых источников и там пока недостающих данных нет. Что делать?
В идеале – подставить прогноз, но у нас его нет. И предположений, которые бы заменили этот прогноз тоже нет. Что делать, чтобы невольно не ввести в заблуждение пользователей отчета? Неплохой вариант – оставить внизу комментарий, о том, что данные неполные. Но мы не застрахованы от того, что нам не попадется пользователь, который не читает такие комментарии. А если даже читает – в зрительной памяти останется именно такая картинка.
Поэтому предлагаем закрасить неполный блок данных другим цветом.
Задача кажется несложной, потому что можно настроить форматирование диаграммы на закладке «Валик». Просто заходим туда и ставим для последнего периода другой цвет и… ничего не получается.
Обычное изменение цвета через настройки не подходит! →
Потому что последний период будет всегда меняться: сначала он перестанет быть неполным, и нужно будет менять его цвет на основной. Затем появится следующий период, его опять нужно будет закрасить и так далее.
Нам необходимо настроить диаграмму так, чтобы неполный период автоматически окрашивался в другой цвет. И это проблема, потому что мы с вами знаем, что:
Потому что это сейчас у нас недостает 3 месяца. Чуть позже это будет 2 месяца, а в следующем году – одиннадцать. И каждый раз размер добавленных элеменов должен меняться динамически в зависимости от того, какие данные есть. Соответственно, все-таки строить такую гистограмму нам потребуется с помощью DAX-формул. Что можно сделать с их помощью:
Мы, конечно, разберем интересный вариант ) Еще и усложним его тем, что возьмем данные не по месяцам, а по годам. Для начала рассчитаем количество поездок за последний год. Чтобы динамически определить, все ли данные за год загружены, сравним даты в таблице данных и в Календаре.
Для расчета maxCalendarData условие фильтрации по году – равенство наибольшему году в таблице данных. Это условие применяется, если в вашем файле есть план или прогноз, когда максимальная дата в Календаре больше, чем последняя дата «закрытого» периода.
Простой авторский взгляд на сквозную 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
Настройка и использование таблиц дат в Power BI Desktop
В Power BI Desktop столбцы с датами определяются автоматически. Затем создаются иерархии дат и другие метаданные для включения модели от вашего имени. Это происходит в фоновом режиме. Эти встроенные иерархии можно использовать в дальнейшем при создании таких компонентов отчета, как визуальные элементы, таблицы, быстрые меры, срезы и т. д. Для этого в Power BI Desktop от вашего имени создаются скрытые таблицы, которые впоследствии вы можете использовать для отчетов и выражений DAX.
См. сведения об этом автоматическом поведении в описании параметра автоматических даты и времени в Power BI Desktop.
Многие специалисты по анализу данных предпочитают создавать собственные таблицы. Можно делать и так. Power BI Desktop позволяет указать таблицу дат для вашей модели и создавать связанные с датами визуализации, таблицы, быстрые меры и другие элементы, используя данные из этой таблицы. Если вы указываете собственную таблицу дат, вы сами управляете иерархиями дат, созданными в модели, и применяете их для быстрых мер и других операций, в которых используется таблица дат вашей модели.
Настройка собственной таблицы дат
Чтобы настроить таблицу дат, выберите нужную таблицу на панели Поля. Затем щелкните таблицу правой кнопкой мыши и последовательно выберите в открывшемся меню Отметить как таблицу дат > Отметить как таблицу дат, как показано на следующем изображении:
Также вы можете указать таблицу, а затем выбрать Отметить как таблицу дат на ленте Моделирование, как показано ниже.
Если вы указываете собственную таблицу дат, в Power BI Desktop выполняются описанные ниже проверки этого столбца и содержащихся в нем данных. Это позволяет убедиться в следующем:
Есть два вероятных сценария, в которых целесообразно создать собственную таблицу данных:
Первый вариант — использование канонических или базовых таблицы данных и иерархий. Это таблица, которая соответствует описанным выше критериям проверки для таблицы дат.
Второй вариант — использование таблицы из служб Analysis Services, например, таблицы с полем измерения даты, в качестве таблицы дат.
Указав таблицу дат, вы сможете выбрать для дат любой столбец в этой таблице. Вы можете указать, какой столбец использовать. Для этого выберите таблицу на панели Поля, щелкните ее правой кнопкой мыши и последовательно выберите Отметить как таблицу дат > Параметры таблицы дат. Появится следующее окно, в котором можно выбрать в раскрывающемся списке столбец для использования в качестве таблицы дат.
Обратите внимание, что при создании собственной таблицы дат в Power BI Desktop иерархии не создаются автоматически, как при выборе другого варианта, когда они встраиваются в модель от вашего имени. Если позднее вы отмените выбор таблицы дат (и у вас больше не будет таблицы дат, настроенной вручную), Power BI Desktop снова автоматически создаст встроенные таблицы дат для столбцов дат в таблице.
Также следует отметить, что когда вы отмечаете таблицу как таблицу дат, встроенная (автоматически созданная) таблица дат в Power BI Desktop удаляется. В таком случае все визуальные элементы или выражения DAX, созданные ранее на основе этих встроенных таблиц, не будут правильно работать.
Присвоение таблице дат надлежащего типа данных
Если вы создаете собственную таблицу дат, необходимо правильно задать тип данных. Для типа данных должно быть установлено значение Дата/время или Дата. Для этого выполните следующие действия:
Выберите свою таблицу дат на панели Поля и при необходимости разверните ее. Выберите столбец, который нужно использовать в качестве даты.
На вкладке Моделирование выберите Тип данных и щелкните стрелку раскрывающегося списка, чтобы отобразились доступные типы данных.
Укажите тип данных для столбца.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.
Простой авторский взгляд на сквозную 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
Применение автоматических даты и времени в Power BI Desktop
Эта статья предназначена для разработчиков моделей данных, создающих модели импорта или составные модели в Power BI Desktop. В ней рассказывается о параметре Автоматические дата и время.
Автоматические дата и время — это параметр загрузки данных в Power BI Desktop. Этот параметр предназначен для поддержки удобного создания отчетов логики операций со временем на основе столбцов дат, загруженных в модель. В частности, он позволяет авторам отчетов, использующим вашу модель данных, фильтровать, группировать и детализировать данные с помощью календарных периодов времени (годы, кварталы, месяцы и дни). При этом вам не нужно явно разрабатывать эти возможности логики операций со временем.
Если этот параметр включен, Power BI Desktop создает скрытую таблицу с автоматическими датой и временем для каждого столбца дат, для которых выполняются все следующие условия:
Принцип работы
Каждая таблица c автоматическими датой и временем на самом деле является вычисляемой таблицей, которая создает строки данных с помощью функции DAX CALENDAR. Каждая таблица также содержит шесть вычисляемых столбцов: День, №Месяца, Месяц, №Квартала, Квартал и Год.
Имена и значения столбцов переводятся и форматируются в Power BI в соответствии с языком модели. Например, если модель была создана на английском языке, названия месяцев и т. д. в ней в по-прежнему будут отображаться на английском языке даже при просмотре с помощью клиента для корейского языка.
В Power BI Desktop также создается связь между столбцом Дата в таблице с автоматическими датой и временем и столбцом дат модели.
Таблица с автоматическими датой и временем содержит полные календарные годы, включая все значения дат, хранящиеся в столбце дат модели. Например, если самое раннее значение в столбце дат равно 20 марта 2016 года, а последнее — 23 октября 2019 года, таблица будет содержать 1461 строку. Представляется одна строка для каждой даты за четыре календарных года с 2016 по 2019. Когда Power BI обновляет модель, также обновляются все таблицы с автоматическими датами и временем. Таким образом, модель всегда содержит даты, охватывающие значения столбцов дат.
Строки таблицы с автоматическими датой и временем (если бы их можно было увидеть) выглядели бы следующим образом.
Таблицы с автоматическими датой и временем всегда скрыты даже от разработчиков моделей. Они не отображаются в области Поля или в схеме представления модели, а их строки нельзя увидеть в представлении данных. Кроме того, выражения DAX не могут ссылаться на таблицу и ее столбец напрямую.
С ними также нельзя работать при использовании функции Анализ в Excel или подключении к модели с помощью конструкторов отчетов, отличных от Power BI.
Таблица также определяет иерархию, предоставляя визуальные элементы с детализацией по годам, кварталам, месяцам и дням.
Если бы на схеме представления модели можно было просмотреть таблицу с автоматическими датой и временем, то она выглядела бы следующим образом (связанные столбцы выделены).
Работа с автоматическими датой и временем
Если для столбца дат существует таблица с автоматическими датой и временем (и этот столбец видимый), авторы отчетов не смогут найти этот столбец как поле в области Поля. Вместо этого они видят развертываемый объект с именем столбца дат. Его можно легко найти, так как он оформлен со значком календаря. Когда авторы отчета развертывают объект календаря, они видят иерархию с именем Иерархия дат. После развертывания иерархии они видят четыре уровня: Год, Квартал, Месяц и День.
Иерархия, созданная на основе автоматических даты и времени, может использоваться для настройки визуального элемента точно так же, как и обычные иерархии. Визуальные элементы можно настроить с использованием всей иерархии Иерархия дат или конкретных уровней иерархии.
Но есть одна дополнительная возможность, которая не поддерживается обычными иерархиями. Когда иерархия с автоматическими датой и временем (или уровнем из иерархии) добавляется в контейнер визуального элемента, авторы отчета могут переключаться между использованием иерархии и столбца дат. Это разумный подход для некоторых визуальных элементов, если требуется только столбец дат, а не иерархия и ее уровни. Начните с настройки поля визуального элемента (щелкните правой кнопкой мыши поле или щелкните стрелку вниз), а затем используйте контекстное меню для переключения между иерархией и столбцом дат.
Кроме того, вычисления модели, написанные на языке DAX, могут ссылаться на столбец дат напрямую или на столбцы скрытой таблицы с автоматическими датой и временем косвенно.
Формула, записанная в Power BI Desktop, может ссылаться на столбец дат обычным способом. Однако на столбцы таблицы с автоматическими датой и временем необходимо ссылаться с помощью специального расширенного синтаксиса. Сначала вы ссылаетесь на столбец дат, а затем указываете точку (.). После этого автоматическое заполнение строки формул позволит выбрать столбец из таблицы с автоматическими датой и временем.
Вот допустимое выражение меры в Power BI Desktop:
Хотя это выражение меры допустимо в Power BI Desktop, это неправильный синтаксис для DAX. На внутреннем уровне Power BI Desktop транспонирует выражение для ссылки на истинный (скрытый) столбец таблицы с автоматическими датой и временем.
Настройка параметра автоматических даты и времени
Автоматические дату и время можно настроить глобально или для текущего файла. Глобальный параметр применяется к новым файлам Power BI Desktop, и его можно включить или отключить в любое время. В новой установке Power BI Desktop оба варианта включены по умолчанию.
Параметр для текущего файла также можно включить или отключить в любое время. Если этот режим включен, создаются таблицы с автоматическими датой и временем. Если этот режим отключен, из модели удаляются все таблицы с автоматическими датой и временем.
Будьте внимательны, когда отключаете параметр для текущего файла, так как это приведет к удалению таблиц с автоматическими датой и временем. Не забудьте исправить все неработающие фильтры отчетов или визуальные элементы, которые были настроены для их использования.
В Power BI Desktop выберите Файл > Параметры и настройки > Параметры, а затем перейдите на страницу Глобальная или Текущий файл. На этих страницах параметр находится в разделе Логика операций со временем.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.