power query разница между датами в днях

Функции DateAdd, DateDiff и TimeZoneOffset в Power Apps

Добавление значения даты и времени или поиск разницы в значениях даты и времени, а также преобразование между местным временем и временем в формате UTC.

Описание

Функция DateAdd добавляет указанное число единиц измерения к значению даты и времени. Результатом является новое значение даты и времени. Можно также вычесть число единиц измерения из значения даты и времени, указав отрицательное значение.

Функция DateDiff возвращает разницу между двумя значениями даты и времени. Результатом является целое число единиц измерения.

Возможные единицы измерения, используемые в обеих функциях: Milliseconds, Seconds, Minutes, Hours, Days, Months, Quarters или Years. По умолчанию обе функции в качестве единиц используют Days.

Функция TimeZoneOffset возвращает число минут между местным временем пользователя и временем в формате UTC.

Можно совмещать функции DateAdd и TimeZoneOffset, чтобы выполнять преобразование между местным временем пользователя и временем в формате UTC. Добавляя результат TimeZoneOffset, можно преобразовать местное время в формат UTC, а вычитая его (добавляя отрицательное значение), можно преобразовать время в формате UTC в местное время.

Синтаксис

DateAdd( DateTime, Addition [, Units ] )

DateDiff( StartDateTime, EndDateTime [, Units ] )

TimeZoneOffset( [ DateTime ] )

Примеры

Во всех примерах предполагается, что текущие дата и время — 13:02 15 июля 2013 года.

Простой пример для функции DateAdd

Простой пример для функции DateDiff

Разница дат с дробными результатами

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

Преобразование в формат UTC

Чтобы выполнить преобразование в формат UTC, добавьте значение TimeZoneOffset для заданного времени.

Например, представьте, что сейчас 13:02 15 июля 2013 года по летнему тихоокеанскому времени США (UTC-7). Чтобы определить текущее время в формате UTC, используйте следующую команду:

По умолчанию TimeZoneOffset вычисляется для текущего времени, поэтому его не требуется передавать как аргумент.

Чтобы увидеть результат, используйте функцию Text в формате dd-mm-yyyy hh:mm. Она вернет 15-07-2013 20:02.

Преобразование из формата UTC

Чтобы преобразовать время из формата UTC в местное время, следует вычесть значение TimeZoneOffset (то есть добавить отрицательное значение) из заданного времени.

Для примера предположим, что значение даты и времени в формате UTC, 20:02 15 июля 2013 года, хранится в переменной StartTime. Чтобы настроить время в соответствии с текущим часовым поясом пользователя, используйте следующую команду:

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

Чтобы увидеть результат, используйте функцию Text в формате dd-mm-yyyy hh:mm. Она вернет 15-07-2013 13:02, если вы находитесь в часовом поясе тихоокеанского времени США (лето).

Источник

Power Query. Погружение в М. Дата, время длительность

Создать значение с типом данных #date

Создаем значение #date.

Дата может быть в далеком будущем.

Дата может быть только после Рождества Христова. Дата до РХ вернет ошибку.

Создать значение с типом данных #time

Создать значение #time.

Секунды дробятся с точностью 100 наносекунд, т. е. до 7 знаков после запятой (точки).

Полночь

Следующий код выводит на экран значение 0:00:00.

Следующий код тоже выводит на экран значение 0:00:00.

Если мы сравним эти значения, то в результате получим FALSE, т. е. PQ не считает эти значения равными.

Преобразуем оба этих значения в число. Первая формула вернет 0, а вторая 1.

#datetime

Создадим значение с типом данных datetime.

Если объединить символом & значения #date и #time, то в результате получится значение #datetime. Порядок слагаемых не имеет значения.

#datetimezone

Значение datetimezone — это datetime и еще количество часов и минут относительно UTC. Если последние 2 числа — это 3 и 0, то это значит +3 часа 0 минут ко времени UTC.

#duration (длительность)

Создаем значение с типом данных длительность.

Длительность может быть отрицательной.

Одну и ту же длительность можно получить разными способами.

Параметры #duration могут быть с разным знаком. Результатом будет сложение всех параметров.

Преобразование типов данных

Преобразовать #date в #datetime. Время получится 00:00:00.

Превратим #datetime просто в #date или просто #time.

Превратить #date или #datetime в #datetimezone

При преобразовании #date или #datetime в #datetimezone в результате получим #datetimezone, в котором 2 последних параметра будут браться из настроек системы.

Превратим #datetimezone в #datetime. При преобразовании берется временная зона из настроек системы.

Сложение и вычитание

#duration можно прибавлять к/вычитать из #date, #time, #datetime, #datetimezone, #duration.

#date можно вычитать из #date, #time из #time, #datetime из #datetime, #datetimezone из #datetimezone.

Умножение и деление

Значение #duration можно умножать и делить на число.

Преобразовать в текст с помощью Text.From

Функция Text.From преобразовывает #date, #time, #datetime, #datetimezone, #duration в текст.

Преобразование в текст с указанием формата с помощью x.ToText

Функции x.ToText преобразовывают #date, #time, #datetime, #datetimezone, #duration с возможностью указать формат и язык. Формула Date.ToText(#date(2021, 9, 1), «d MMMM yy», «en-US») превратит дату 01.09.2021 в 1 September 21.

OLE Date

OLE Date — это количество дней, которое прошло от начальной даты. Формула Date.From(0) вернет начальную дату в Power Query. Формула Date.From(20) вернет дату, которая отстоит на 20 дней от начальной даты.

Преобразовать #date, #time, #datetime, #datetimezone, #duration в OLE Date.

Источник

Сравнение временных периодов

Иногда бывает очень полезно сравнить данные с предыдущим периодом. Подобная функциональность есть в Google Analytics и Яндекс.Метрике. Сделаем такую же.

Часть 1. Power Query

Для начала нам нужно создать два календаря. Первый основной, второй для сравнения. Это можно быстро сделать в Power Query взяв в качестве календаря соответствующий столбик основной таблицы.

Также нам необходимо в первом календаре продублировать столбец Date. Второй столбец дат — DateVisual будет нужен для визуализации и для связи с основной таблицей. Тогда как столбцы Date в обоих календарях будут предназначены только для фильтрации.

Часть 2. DAX

Тут нам нужно создать две меры:

_DateDelta — отвечает за подсчет разницы между диапазонами (фактически это кривой костыль, потому что по-нормальному вычислить разницу не получилось, но есть подозрение, что существует более разумное решение)

_ClicksPrev — вычисление кликов за предыдущий период

Часть 3. Визуализация

Для начала свяжем столбец DateVisual из главного календаря с полем Date из основной таблицы.

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

Теперь создаем визуализацию в виде графика. Берем clicks из главной таблицы и меру _ClicksPrev как данные. А в качестве оси — DateVisual из первого календаря.

Собственно — всё.
При изменении диапазонов дат в фильтрах графики соответственно изменяются. Также, поскольку мера использует только первую дату из диапазона второго календаря, то можно залочить вторую, чтобы не расстраивать пользователя.

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

Источник

Функции для работы с датами и временем

В этой статье

Эти функции позволяют создавать значения параметров и zone и управлять ими.

Дата и время

Расчет разницы между датами из одной колонки в Power Query

Сообщений: 24 Регистрация: 27.05.2020

У моей модели в PBI есть одна колонка с датами, даты расположены хаотично, но это не принципиально.

В Power Query необходимо создать пользовательскую колонку, в которой будет рассчитано количество месяцев между минимальной и максимальной датой.

Как это сделать? Желательно в двух вариантах:

1. в котором не нужно будет создавать дубликат столбца с датами, т.е. минимальная и максимальная дата определяется только из одного столбца

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

Сообщений: 6143 Регистрация: 22.02.2017

Умею писать хаотичный код, но не уверен что вам это поможет.

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

Изменено: PooHkrd — 27.05.2020 17:35:21

Вот горшок пустой, он предмет простой…

Сообщений: 254 Регистрация: 03.12.2018

Георгий Шкуро, грубо: