Перейти к содержимому

Глава 10. Работа с датой, временем и длительностью

Это продолжение перевода книги Грегори Декер, Рик де Гроот, Мелисса де Корте. Полное руководство по языку М Power Query. Учитывая важность дат в отчетах, понимание того, как обрабатывать даты, время и длительности в Power Query, является критически важным навыком. Действительно, почти каждая модель данных Power BI имеет или должна иметь таблицу дат. Библиотека М включает более 100 функций для работы с датами, временем и длительностями. В этой главе мы рассмотрим многие из них, а также примеры, которые улучшат ваше понимание M.

Мои комментарии набраны с отступом.

Предыдущая глава       Содержание    Следующая глава

Скачать заметку в формате Word или pdf

Рекомендуем выполнять примеры в редакторе Power Query. Так вы получите более глубокое их понимание. Исходные файлы включены в репозиторий GitHub.

Даты

Даты – это основа временного анализа. Существует около 60 функций для работы с датами. На самом деле даты – просто числа, представляющие количество дней до или после контрольной даты. Покажем это на примере. Создайте пустой запрос и в расширенном редакторе введите:

Пройдите Средства для списков –> Преобразование –> В таблицу. В открывшемся окне кликните ОK. Щелкните правой кнопкой мыши заголовок Column1 и выберите Создать дубликат столбца. Щелкните правой кнопкой мыши заголовок столбца Column1 и выберите Изменить тип –> Дата:

Рис. 10.2. Преобразование столбца в дату

Число 0 соответствует контрольной дате 30.12.1899. Каждое положительное или отрицательное приращение на 1 увеличивает или уменьшает дату на один день. В М существуют ограничения на обработку. Невозможно представить даты до 1/1/1 и после 31/12/9999. Преобразование числа в дату имеет другие ограничения. Максимум тот же, 31/12/9999 (2 958 465), а вот минимум = -657 434, что соответствует 1/1/100. Эти ограничения кажутся произвольными. Они не соответствуют количеству байтов для хранения числа, и границы не являются симметричными относительно контрольной даты.

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

Тот факт, что даты представлены в виде чисел, возможно, не так важен в M, как, например, в DAX или Excel. То что даты являются числами, позволяет в DAX выполнять над ними арифметические операции. Однако в M попытка выполнить сложение дат приведет к ошибке:

Рис. 10.3. Ошибка при добавлении числа к дате

Нужно сначала преобразовать дату в число, сложить два числа, и преобразовать число в дату:

Этот код возвращает дату 5 января 2024 года. В окне предварительного просмотра мы увидим 05.01.2024 или 01.05.2024 в зависимости от региональных настроек.

Сначала мы задаем дату функцией #date с тремя аргументами: год, месяц и день. Функция Number.From приводит тип данных date к number, а затем функция Date.From выполняет обратное преобразование. Функция Date.From принимает значение text, datetime, datetimezone или number в качестве первого аргумента и пытается преобразовать это значение в дату. Если указан какой-либо другой тип данных или Date.From не может разобрать значение и определить дату, возвращается ошибка.

Необязательный второй параметр, Culture, позволяет указать региональные настройки. Например, если вы используете Power BI Desktop на компьютере с локальными настройками России (ru-RU), а получаете дату в виде текста из США (en-US) или Великобритании (en-GB), то следующие выражения позволят верно распознать даты:

Рис. 10.3а. Распознавание дат зависит от региональных настроек

С датами можно выполнять некоторые арифметические операции. Например…

… вернет длительность (Duration) со значением 4.00:00:00, что соответствует четырем дням.

Рассмотрим некоторые функции дат, доступные в M. А начнем с запроса, возвращающего список дат для 2024, 2025 и 2026 годов:

На шаге Source с помощью функции List.Generate мы создаем список от начального #date(2024, 1, 1) до конечного #date(2026, 12, 31) значения с шагом один день Date.AddDays(). В М также есть функции для добавления других стандартных временных периодов: Date.AddWeeks, Date.AddMonths, Date.AddQuarters и Date.AddYears.

Существуют функции для извлечения компонентов даты, и мы можем добавить их в наш код:

Еще один способ извлечения компонентов даты – функция Date.ToRecord. Следующее выражение возвращает запись, содержащую пару поле/значение для года, месяца и даты:

Рис. 10.3б. Извлечение компонентов даты в запись

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

В стандартной библиотеке есть также семейство функций Date.StartOf и Date.EndOf для каждого временного периода – день, неделя, месяц, квартал, год. Функции этого семейства возвращают значение даты и времени, указывающее начало или конец периода. Например, выражение…

… возвращает 29.02.2024, т.е., дату окончания месяца для 14 февраля 2024 года.

Для семейств функций Date.StartOf и Date.EndOf выходные данные зависят от типа входных данных. При подаче на вход типа date, datetime или datetimezone в качестве выходных данных создается соответствующий тип. Например, выражение …

… вернет значение 2024-02-29T23:59:59.9999999 с типом данных datetime, а выражение …

… 2024-02-29T23:59:59.99999999-05:00 с типом данных datetimezone с часовым поясом Восточного стандартного времени (EST) или Всемирное координированное время (UTC) минус 5.

Для типов данных date существуют три дополнительных семейства функций: IsInCurrent, IsInNext и IsInPrevious. Каждое из этих семейств включает функции для стандартных временных периодов: дня, недели, месяца, квартала и года. Эти функции возвращают логические значения в зависимости от того, находится ли входная дата, в пределах текущего периода. Например, года для функции IsInCurrentYear. Расчеты основаны на текущей дате и времени системы, поэтому при запуске Power BI Desktop на локальном компьютере и в службе Power BI с клиентом, находящимся в другом часовом поясе, могут возвращаться разные результаты.

Семейства функций IsInNextN и IsInPreviousN также покрывают все временные периоды. Кроме первого параметра типа date, функции имеют второй параметр, указывающий количество временных единиц (положительных или отрицательных), чтобы определить, находится ли текущая дата в указанном диапазоне. Например, функция Date.IsInNextNWeeks( #date(2024, 2, 14), 2) определяет, находится ли дата в диапазоне двух следующих недель.

Дополнительно есть функция IsIn для текущего года – Date.IsInYearToDate. Она возвращает true или false в зависимости от того, находится ли переданная в функцию дата в пределах текущего года до текущей даты.

Наконец, есть функция, которая преобразует тип date в text – Date.ToText с необязательными аргументами Format и Culture. Рик де Гроот ведет список кодов, которые могут быть использованы в параметре Format:

Рис. 10.3в. Коды аргумента Format для типов данных date

Таким образом, код…

… возвращает "Wednesday, February 14, 2024 A.D.". Этот результат может варьироваться в зависимости от настроек культуры. В коде для отображения рис. 10.3в я добавил "ru-RU", чтобы вернуть значения для региональных настроек.

Функция Date.FromText наоборот преобразует текстовое представление даты в тип данных date, и  также поддерживает параметры Format и Culture.

Таблица календаря

Как упоминалось ранее, почти все модели данных Power BI имеют или должны иметь таблицу дат. Часто они берутся из хранилища данных. В случае отсутствия последнего можно использовать знаменитую функцию Extended Date Table Мелиссы де Корте. Код функции слишком длинный, чтобы включать его в книгу, но функция Melissa de Korte’s Date Table есть в файле для этой главы.

Рис. 10.4. Функция расширенной таблицы дат Melissa de Korte’s Date Table Function

Для работы функции требуются два параметра, StartDate и EndDate. Также есть четыре необязательных параметра:

  • FYStartMonthNum: номер месяца, с которого начинается финансовый год; 1, если опущено.
  • Holidays: выберите запрос (и столбец), содержащий список праздничных дат.
  • WDStartNum: переключение нумерации дней недели с 0-6 (по умолчанию) на 1-7, введя 1.
  • AddRelativeNetWorkdays: если значение равно true, в таблицу дат добавляется столбец Relative Networkdays

На рис. 10.4 показана конфигурация для создания таблицы дат между 2020 и 2026 годами, где финансовый год начинается в июле, дни недели пронумерованы от 1 до 7, а столбец Relative Networkdays включен в таблицу дат. Праздники не указаны. При нажатии кнопки Вызвать создается запрос Вызванная функция с кодом:

Запрос можно переименовать в Календарь. Для дат с 01.01.2020 по 31.12.2026 был создан 61 столбец. Обратите внимание:

  • Столбец Fiscal Week начинается с понедельника и может содержать менее 7 дней в первой и/или последней неделе финансового года.
  • В столбце IsWeekDay не учитываются праздничные даты.
  • В столбце IsBusinessDay учитываются необязательные даты праздников.
  • Столбцы IsPYTD и IsPFYTD сравнивают предыдущий номер дня года с текущим номером дня года, поэтому даты не совпадают в високосные годы.
  • Столбцы Fiscal не будут добавлены, если параметр FYStartMonthNum опущен.

Одной из замечательных особенностей этой таблицы дат является реализация смещений в столбцах CurrYearOffset, CurrQuarterOffset, CurrMonthOffset, CurrWeekOffset и CurrDayOffset. Эти столбцы значительно упрощают логику вычислений с датами и временем в DAX, как показано в статье Брайана Джулиуса Time Intelligence In DAX: How To Dynamically Select Starting Period.

Другие форматы дат

Даты, описанные здесь, используются в большинстве стран мира и основаны на так называемом григорианском календаре. Григорианский календарь был введен в 1582 году папой Григорием XIII в качестве замены юлианского календаря. Тем не менее, существуют и другие календари и форматы дат, используемые в различных уголках мира и в некоторых научных областях.

Юлианские дни

Номер юлианского дня (Julian Day Number, JDN) – это порядковый номер дня в непрерывной системе отсчета, начинающейся с 1 января 4713 года до н. э. JDN используется в различных научных и вычислительных областях, в том числе:

  • Измерение времени: Юлианские дни предоставляют непрерывную и равномерную систему измерения времени, которая широко используется в астрономии и астрофизике для указания даты и времени наблюдений, вычислений и событий. Это упрощает вычисления интервалов между двумя датами.
  • Эфемериды: Астрономические эфемериды, которые предоставляют положения небесных объектов во времени, часто используют юлианские дни для своей временной шкалы.
  • Универсальная система датирования: Юлианские дни предлагают единую и непрерывную систему датирования, полезную в исторической хронологии. Это позволяет легко рассчитывать временные интервалы и особенно ценно для исторических событий, охватывающих длительные периоды.
  • Данные с временной меткой: В геофизике и науках о Земле данные, собираемые с течением времени, такие как сейсмические события или экологические наблюдения, часто записываются с использованием JDN. Это позволяет упорядочить события хронологически.
  • Вычислительная эффективность: Юлианские дни удобны для компьютерных алгоритмов и программирования, так как они предоставляют одно число, упрощающее вычисления дат и времени. Это особенно полезно при работе с большими наборами данных или выполнении вычислений, связанных с временными интервалами.
  • Навигационные расчеты: В навигации юлианские дни используются для расчета положений небесных тел и определения времени небесных событий. Они предоставляют стандартизированный способ представления времени для навигационных целей.
  • Расчеты високосных дней и годов: Юлианские дни упрощают расчеты, связанные с високосными днями и годами, поскольку они представляют собой непрерывный счет дней, не зависящий от различий в длине месяцев или годов.

В файле этой главы есть функция fnJulianDay для преобразования григорианских дат в JDN. Следующий запрос возвращает таблицу за 1987 год, содержащую столбец для юлианских дней:

Рис. 10.4а. Григорианские даты и юлианские дни

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

Форматы дат ERP Oracle

Некоторые системы хранят даты в разных форматах. В качестве примера можно привести несколько версий популярной программы J. D. Edwards Enterprise Resource Planning (ERP), которая в настоящее время принадлежит Oracle. Эта система хранит даты с помощью флага столетия в следующем формате: cyyddd. Здесь c – флаг века, где 0 соответствует 1900, а 1 – 2000. Год хранится в виде двузначного числа от 0 до 99. День хранится как номер дня года. Для преобразования этого формата даты в тип данных date воспользуйтесь функцией fnJDEToDate.

Пользовательские функции даты

Создание пользовательских функций для работы с датами может сэкономить массу времени. Мы обсудим несколько таких функций.

Будни

Существует множество способов учитывать течение времени. Некоторые организации отслеживают ход времени в рабочих днях. Пользовательская функция CategorizeDay классифицирует дни на рабочие и нерабочие:

Она принимает один аргумент – дату. Функция Date.DayOfWeek возвращает число от 0 до 6, где 0 – понедельник. Необязательный параметр, перечисление Day.Monday, переключает выходные данные со значения по умолчанию 0 для воскресенья на 0 для понедельника.

Пользовательская функция WorkingDays определяет количество рабочих дней между двумя датами:

Функция принимает три параметра: дату начала, дату окончания и необязательный список праздничных дней. Сначала мы проверяем наличие необязательного параметра, и, если он отсутствует, создаем пустой список. Затем мы вычисляем количество дней, которые нужно включить в наш диапазон дат от startDate до endDate, вычитая две даты, преобразуя полученную длительность в число и добавляя 1. Затем мы используем функцию List.Dates для создания списка дат, включенных в наш диапазон (также могли бы использовать List.Generate). Следующее выражение выбирает сгенерированные элементы списка, используя ту же логику, что и наша функция CategorizeDay. Даты, которые есть в списке holidayDates не выбираются. Наконец, мы подсчитываем элементы в отфильтрованном списке.

Учитывая ранее подсмотренный прием, код можно немного упростить: изменить List.Contains(holidayDates, _) на List.Contains(holidays ?? {}, _) и удалить шаг holidayDates.

Скользящее среднее

Распространенным сценарием в аналитике данных является расчет скользящего среднего для определенной метрики. Это можно сделать как с помощью DAX, так и на стороне Power Query. Пользовательская функция MovingAvg добавляет столбец со скользящим средним в таблицу:

Функция принимает три параметра: таблицу, имя столбца для которого вычисляется среднее и размер окна скользящего среднего.

Chat GPT. В контексте вычисления скользящего среднего размер окна (window size) определяет количество последовательных данных, которые будут учитываться при вычислении среднего значения.

Функция помещает в буфер переданный в аргументе столбец, добавляет столбец индекса, отсчитываемый от 1. Затем добавляет столбец скользящего среднего MovingAvg, с параметром windowSize, и возвращает исходную таблицу вместе со столбцом MovingAvg.

Предположим, что функция используется в таблице фактов, содержащей ежедневную информацию о продажах с размером окна 30, вызов этой функции вернет таблицу со скользящим средним, рассчитанным для каждой строки за последние 30 дней. Функцию можно адаптировать к скользящей сумме или иному вычислению, заменив List.Average на List.Sum или иной агрегатор.

Перейдем к обсуждению времени.

Время

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

… который возвращает таблицу:

Рис. 10.5. Таблица часов

Число 0 в столбце Column1 соответствует полуночи. В столбце Multiplication мы разделили Column1 на 24, чтобы получить долю дня. Эти значение при преобразовании в тип time возвращают часы. Обратите внимание, что значение 1 возвращает ошибку.

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

Аналогично датам, есть несколько функций для извлечения компонентов времени:

Также существует функция Time.Record, которая извлекает компоненты времени в запись. Тип данных времени имеет конструктор #time, принимающую три параметра: час, минута и секунда. Можно предположить, что добавление времени к дате приведет к получению типа данных datetime, но это не так. Выражение…

… приводит к ошибке Expression.Error: Не удается применить оператор + к типам Date и Time.

А вот оператор конкатенации работает. Выражение…

… вернет 14.02.2024 17:00:00.

Функции Time.StartOfHour и Time.EndOfHour возвращают начало и конец часа. Тип результата зависит от типа входных данных – time, datetime или datetimezone. Например:

Функции Time.From и Time.FromText преобразуют входное значение в тип данных time. Функция Time.From принимает значения text, datetime, datetimezone или number (≥ 0 и < 1). Обе функции включают необязательный параметр Culture.

Функция Time.ToText преобразует тип данных time в текст. Представлением управляют необязательные параметры Format и Culture. Более подробную информацию о различных вариантах форматирования можно найти на веб-сайте Рика де Гроота Gorilla BI.

Создание таблицы времени

Для создания отчетов могут потребоваться измерения времени с минутной или секундной детализацией. В приложенном файле есть запрос Time Table Minute:

Запрос возвращает таблицу с минутной детализацией:

Рис. 10.5а. Таблица времени с минутной детализацией

Если нужна детализация на секундном уровне, изучите код запроса Time Table Second.

Классификация смен

Можно создать функцию для классификации времени на первую, вторую и третью смены. Предполагая, что первая смена длится с 8 утра до 4 вечера, вторая – с 4 вечера до полуночи, а третья – с полуночи до 8 утра, следующая функция классифицирует время, переданное в качестве параметра, как попадающее в определенную смену (запрос ShiftClassification):

Дата и время

Поскольку даты – это целые числа, основанные на эталонной дате, а время – десятичные числа, выражающие доли дня, тип данных datetime может быть представлен в виде десятичного числа, где целая часть представляет дату, а десятичная – время. Например, следующее выражение…

… возвращает 14.02.2024 12:00:00

Отдельные компоненты даты и времени можно извлечь из типа данных datetime с помощью функций DateTime.Date и DateTime.Time. Функция DateTime.ToRecord возвращает запись с парами поле-значение для года, месяца, дня, часа, минуты и секунды.

Для datetime доступен конструктор #datetime. Следующее выражение…

… вернет 14.02.2024 17:00:00.

Функции, DateTime.LocalNow и DateTime.FixedLocalNow, возвращают текущую дату и время системы. DateTime.LocalNow возвращает разные значения при каждом обращении к функции в пределах одного запроса. Несколько вызовов DateTime.FixedLocalNow – возвращают одно и то же значение.

Существуют семейства функций IsInCurrent, IsInNext и IsInPrevious. Каждое из семейств включает отдельные функции для компонентов времени (часа, минуты и секунды). Семейства IsInNext и IsInPrevious включают подсемейства IsInNextN и IsInPreviousN. Все эти функции возвращают логическое значение в зависимости от того, соответствует ли значение datetime условиям.

Функция DateTime.FromFileTime преобразует время файла Windows в стандартный формат datetime. Файловое время Windows записывается как общее количество 100-наносекундных интервалов с 12:00 1 января 1601 г. н.э. по UTC. Эта отправная точка используется в качестве эталона для согласованности записей времени файлов в системах Windows. Таким образом, выражение…

… возвращает 14.02.2024 20:00:00, если вы находитесь в Москве.

Функция DateTime.AddTimeZone добавляет сведения о часовом поясе к типу данных datetime, возвращая тип данных datetimezone. Продолжая предыдущий пример, следующее выражение…

… возвращает тип данных datetimezone, который добавляет информацию о часовом поясе для Москвы 14.02.2024 20:00:00 +03:00.

Часовые пояса

Несмотря на то, что часовые пояса в высшей степени практичны и относительно просты для понимания, они вызывали проблемы с программированием с самого начала развития вычислительной техники. К счастью, язык M включает в себя тип данных datetimezone с множеством функций, которые облегчают работу с датой, временем и часовым поясом.

В отличие от типов данных date, time и datetime, типы данных datetimezone не могут быть представлены только в виде десятичных чисел. Фактически, создание значения datetimezone с помощью DateTimeZone.From(45336.5) добавляет дополнительные сведения о часовом поясе локальной системы, возвращая 14.02.2024 12:00:00 +03:00 для ПК, работающего в Москве.

Для типа данных datetimezone существует два дополнительных элемента: ZoneHours и ZoneMinutes. Их можно извлечь с помощью DateTimeZone.ZoneHours и DateTimeZone.ZoneMinutes. Функция DateTimeZone.ToRecord возвращает запись:

Рис. 10.5б. Запись, возвращаемая функцией DateTimeZone.ToRecord

Функция #datetimezone принимает восемь обязательных параметров.

Функции DateTimeZone.LocalNow и DateTimeZone.FixedLocalNow возвращают текущее локальное системное время, а функции DateTimeZone.UtcNow и DateTimeZone.FixedUtcNow – текущее время системы по UTC. Функция DateTimeZone.ToUtc преобразует значение datetimezone во время UTC. Например, выражение…

… возвращает 14.02.2024 9:00:00 +00:00 в Москве. Здесь исходное значение полудня 14 февраля 2024 года переносится на 3 часа назад. Это имеет смысл, учитывая, что часовой пояс Москвы равен +3, т.е. что часовой пояс Москвы на 3 часа опережает время UTC.

Функция DateTimeZone.ToLocal преобразует значение datetimezone в локальное время компьютера, вычисляющего выражение. Например, выражение…

… возвращает 14.02.2024 20:00:00 +03:00 при вычислении на компьютере, работающем в Москве.

Функция DateTimeZone.RemoveZone удаляет сведения о часовом поясе, возвращая тип данных datetime. Функция DateTimeZone.SwitchZone заменяет сведения о часовом поясе часовым поясом, заданным вторым и третьим параметрами этой функции. Обратите внимание, что функция SwitchZone не просто заменяет часовой пояс, а преобразует значение datetimezone в новый часовой пояс. Таким образом, выражение…

… эквивалентно примеру с использованием функции ToUtc и возвращает точно такое же значение, 14.02.2024 9:00:00 +00:00. Таким образом, функцию SwitchZone можно рассматривать как более универсальную функцию, чем более специфические функции ToUtc и ToLocal.

Функция DateTimeZone.FromFileTime работает аналогично функции DateTime, но добавляет сведения о часовом поясе, извлеченные из локальной системы, вычисляющей выражение.

Исправление времени обновления данных

В отчеты часто включают время последнего обновления данных. Служба Power BI записывает время обновления в часовом поясе региона клиента Power BI. Это может не совпадать с часовым поясом страны, в которой пользователи просматривают отчет. Проблему можно устранить, применяя пользовательскую функцию для вычисления времени обновления, сохранения даты и времени в виде таблицы в модели данных, а затем отображения этой даты и времени на страницах отчета:

Функцию разработал Рик де Гроот. Она принимает два параметра: летнее и зимнее смещение от UTC. Во-первых, DateTimeZone.UtcNow получает дату, время и часовой пояс системы. Функция Date.From извлекает дату. Предполагая, что летнее время начинается в последнее воскресенье марта, а зимнее – в последнее воскресенье октября, следующие две строки кода определяют даты начала летнего и зимнего времени. Смещение вычисляется на основе текущей даты в сравнении с датами перехода на летнее и зимнее время. Затем время настраивается с помощью функции DateTimeZone.SwitchZone.

Длительность

В отличие от модели данных службы Analysis Services, язык M включает тип данных для длительности. Отсутствие типа данных duration в службах Analysis Services означает, что вычисления, включающие длительность лучше обрабатывать в Power Query, а не в столбцах или мерах DAX. Однако также важно понимать, что столбцы типа данных duration в Power Query преобразуются в десятичные числа при загрузке в модель данных. Выражение…

… возвращает таблицу с одной строкой и одним столбцом с типом данных duration со значением 1 час. В модель данных загрузится значение 0,04166666666666667, т.е. 1 час/24 часа. Чтобы отобразить формат длительности в отчетах, необходимо выполнить преобразование данных, которое либо окончательно превратит значение в текст с помощью функции FORMAT DAX, либо применить пользовательский формат, чтобы сохранить значение в виде числа.

Тип данных duration имеет конструктор – #duration, принимающий четыре параметра: дни, часы, минуты и секунды. Следующее выражение…

… возвращает длительность 5.02:15:33.

Тип данных duration можно добавить к типу данных date, time, datetime или datetimezone с помощью оператора сложения (+). Таким образом, следующее выражение…

… возвращает дату 14.02.2024.

А выражение…

… возвращает время 19:15:33.

Компоненты длительности можно извлечь с помощью функций Duration.Days, Duration.Hours, Duration.Minutes и Duration.Seconds. Функция Duration.ToRecord возвращает запись с 4 полями: Days, Hours, Minutes и Seconds.

Имеется семейство функций Duration.Total, приводящее длительность к дробному числу дней, часов, минут или секунд. Например, выражение…

… возвращает значение 94 212.

Поскольку движок службы Analysis Services не поддерживает тип данных длительности, рекомендуется выполнить вычисления длительности в Power Query, а затем использовать одну из функций семейства Duration.Total для преобразования столбца duration в число (например, секунд) перед загрузкой в модель данных. После сохранения в секундах существует множество стандартных методов DAX для преобразования значения в длительность. См., например, заметку Chelsie Eiden’s Duration в коллекции быстрых мер сайта сообщества Power BI.

Продолжительность работы

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

Функция принимает четыре параметра: фактическое время начала и окончания работы, а также время начала и окончания рабочего дня. Операторы if сравнивают фактическое время с нормативным. Продолжительность равна разности измененного времени начала и окончания.

Для преобразования количества дней и рабочих часов в день в длительность можно использовать функцию:

Саммари

В этой главе мы рассмотрели дату, время, дату и время, часовые пояса и длительность. Мы привели несколько практических примеров, включая знаменитую расширенную таблицу дат Мелиссы де Корте. Мы также показали примеры расчета количества рабочих дней между двумя датами и как рассчитать скользящее среднее. Мы продемонстрировали создание таблицы измерения времени на минутном и секундном уровнях детализации.

В следующей главе мы продолжим наше исследование M, и подробно обсудим сравнение, замену, объединение и разделение значений.

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

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