power bi количество дней между датами
Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)
Ограничение таблиц из дат в Power BI и Power Pivot: DAX функции DATESBETWEEN и DATESINPERIOD
Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы обсудим DAX функции DATESBETWEEN и DATESINPERIOD, создающие в Power BI и PowerPivot ограниченные таблицы из дат.
Эти функции не создают непрерывный календарь, они, всего лишь, берут именно тот перечень дат, который содержится в исходном столбце и ограничивают его. Если в исходной таблице имеются пропуски, то DATESBETWEEN или DATESINPERIOD вернут ограниченный столбец из дат, также, с этими же пропусками.
Чтобы создавать непрерывные календари, для этого в языке DAX имеются другие функции, о которых Вы можете прочитать в этой статье.
Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.
Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».
А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.
DAX функция DATESBETWEEN в Power BI и Power Pivot
DATESBETWEEN () — ограничитель календаря по произвольным датам. Создает ограниченную таблицу из дат, начиная со стартовой и заканчивая конечной датами, расположенными в исходном столбце.
! — Стартовые и конечные даты, указанные в параметрах функции, входят в состав выводимых дат.
Пример формулы на основе DAX функции DATESBETWEEN.
В Power BI имеется исходная таблица «Продажи», содержащая даты и сумму продаж за 2 года с 1.01.2017 по 31.12.2018. Но, продажи имеются не за каждый день. Обратите внимание, что 30 декабря 2018 года отсутствует, потому что продаж не было:
В определенных расчетах нам могут понадобиться даты этих продаж, но не все, а ограниченные, например, только за 2018 год. Ограничить даты можно многими способами, начиная от пользовательских фильтров в разделе «Отчеты» в Power BI Desktop, заканчивая разнообразными формулами и функциями внутри самого языка DAX.
Так как в этой статье мы разбираем DATESBETWEEN, то давайте разберем пример ограничения при помощи этой функции, тем более, что она, как раз, для этого и существует.
Создадим во вкладке «Моделирование» в Power BI Desktop вычисляемую таблицу по следующей формуле на основе DATESBETWEEN:
Этой формулой мы создали таблицу с датами продаж, ограниченными только 2018 годом:
Обратите внимание, что DATESBETWEEN возвратила именно тот набор дат, который был в исходном столбце, то есть, 30 декабря 2018 года в этой таблице тоже нет.
DAX функция DATESINPERIOD в Power BI и Power Pivot
DATESINPERIOD () — ограничитель календаря по типовым интервалам. Создает ограниченную таблицу из дат, начиная со стартовой даты, расположенной в исходном столбце и продолжая в течение указанного типового интервала (количеством дней, месяцев, кварталов, лет).
DATESINPERIOD очень похожа на выше рассматриваемую функцию в первом разделе этой статьи. Разница заключается лишь в том, что DATESINPERIOD ограничивает исходный набор дат именно определенными целыми интервалами.
Продолжая рассматривать практический пример, который мы разбирали с первой функцией выше, давайте напишем, формулу, ограничивающую даты продажи 2018 годом, но теперь, на основе DAX функции DATESINPERIOD:
В первом параметре мы указали исходный столбец, во втором — стартовую дату, с которой нужно начать ограничение, в третьем — количество периодов, в нашем случае, 12. Ну и, в четвертом — наименование самого периода — я выбрал «MONTH», то есть, месяц.
Результатом выполнения этой формулы на основе DATESINPERIOD, получился столбец из дат, начиная с 1 января 2018 года и далее, продолжая на период в 12 месяцев, до 31 декабря 2018 года:
Обращаю Ваше внимание, что и в данном случае, как это было и с первой рассматриваемой функцией, 30 декабря здесь также отсутствует, так как рассматриваемые в этой статье обе функции не выводят список из непрерывных дат, а выводят именно то, что имеется в исходном столбце.
Сами по себе DATESBETWEEN и DATESINPERIOD обычно просто так не используют. А вставляют их в другие формулы в сочетании с другими функциями.
Также, не нужно путать столбцы из дат, которые создают DATESBETWEEN либо DATESINPERIOD с календарем. Для создания календаря есть специализированные функции, ссылку на них я уже давал выше. А рассматриваемые функции в этой статье — являются именно ограничителем либо исходного столбца дат, либо большого непрерывного календаря.
На этом, с функциями ограничивающими таблицу дат в Power BI и Power Pivot, в этой статье все.
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи? 
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D
Простой авторский взгляд на сквозную 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
Применение автоматических даты и времени в 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 выберите Файл > Параметры и настройки > Параметры, а затем перейдите на страницу Глобальная или Текущий файл. На этих страницах параметр находится в разделе Логика операций со временем.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.
Настройка и использование таблиц дат в 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, созданные ранее на основе этих встроенных таблиц, не будут правильно работать.
Присвоение таблице дат надлежащего типа данных
Если вы создаете собственную таблицу дат, необходимо правильно задать тип данных. Для типа данных должно быть установлено значение Дата/время или Дата. Для этого выполните следующие действия:
Выберите свою таблицу дат на панели Поля и при необходимости разверните ее. Выберите столбец, который нужно использовать в качестве даты.
На вкладке Моделирование выберите Тип данных и щелкните стрелку раскрывающегося списка, чтобы отобразились доступные типы данных.
Укажите тип данных для столбца.
Дальнейшие действия
Дополнительные сведения, связанные с темой этой статьи, см. в следующих ресурсах.