Использование функций логики операций со временем DAX

  • 5 мин

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

Примечание

Многие функции логики операций со временем DAX связаны со стандартными периодами дат, в частности с годами, кварталами и месяцами. Если у вас нерегулярные периоды времени (например, финансовые месяцы, которые начинаются с середины календарного месяца) или необходимо работать с неделями или другими периодами времени (часы, минуты и т. д.), функции логики операций со временем DAX не будут полезны. Вместо этого необходимо использовать функцию CALCULATE и передать созданные вручную фильтры даты или времени.

Требование к таблице дат

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

  • Она должна содержать столбец типа данных Date (или date/time), известный как столбец даты.
  • Столбец даты должен содержать уникальные значения.
  • Столбец даты не должен содержать ПУСТЫЕ ЗНАЧЕНИЯ.
  • В столбце даты не должно быть отсутствующих дат.
  • Столбец даты должен охватывать полные годы. Год не обязательно должен быть календарным (январь–декабрь).
  • Таблица дат должна отображаться в виде таблицы с датами.

Дополнительные сведения см. в статье Создание таблиц дат в Power BI Desktop.

Способы формирования сводных данных по времени

Одна группа функций логики операций со временем DAX связана с формированием сводных данных по времени:

  • DATESYTD — возвращает таблицу с одним столбцом, которая содержит даты с начала года (YTD) для текущего контекста фильтра. Эта группа также включает функции DAX DATESMTD и DATESQTD с начала месяца (MTD) и с начала квартала (QTD). Эти функции можно передать в качестве фильтров в функцию DAX CALCULATE.
  • TOTALYTD — вычисляет выражение для YTD в текущем контексте фильтра. Также включены эквивалентные функции DAX за период QTD и MTD TOTALQTD и TOTALMTD.
  • DATESBETWEEN — возвращает таблицу, содержащую столбец с датами, начиная с даты начала и заканчивая датой окончания.
  • DATESINPERIOD — возвращает таблицу, содержащую столбец дат, который начинается с даты начала и продолжается в течение указанного количества интервалов.

Примечание

Хотя функция TOTALYTD проста в использовании, число передаваемых критериев фильтра ограничено одним. Если необходимо применить несколько критериев фильтра, используйте функцию CALCULATE, а затем передайте функцию DATESYTD в качестве одного из критериев фильтра.

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

TOTALYTD(<expression>, <dates>, [, <filter>][, <year_end_date>])

Функция требует выражение и, как принято для всех функций логики операций со временем, ссылку на столбец даты помеченной таблицы дат. При необходимости можно передать один критерий фильтра или дату окончания года (требуется только в том случае, если год не заканчивается 31 декабря).

Скачайте и откройте файл Adventure Works DW 2020 M07.pbix. Затем добавьте в таблицу Sales следующее определение меры, которое вычисляет доходы с начала года. Отформатируйте меру как валюту с двумя десятичными разрядами.

Revenue YTD =
TOTALYTD([Revenue], 'Date'[Date], "6-30")

Значение даты окончания года "6-30" соответствует 30 июня.

На странице 1 отчета добавьте в визуальный элемент матрицы меру Revenue YTD. Обратите внимание, что она формирует сводные данные доходов с начала года до отфильтрованного месяца.

На изображении показан визуальный элемент матрицы с группировкой по годам и месяцам в строках и сводных данных Revenue и Revenue YTD. Значения с начала года выделены.

Сравнения по времени

Другая группа функций логики операций со временем DAX связана со сдвигом периодов времени:

  • DATEADD — возвращает таблицу, содержащую столбец дат, сдвинутых на указанное количество интервалов в будущее или в прошлое от дат в текущем контексте фильтра.
  • PARALLELPERIOD — возвращает таблицу, содержащую столбец с датами, представляющими период, параллельный датам в указанном столбце дат, в текущем контексте фильтра. Даты в нем смещены на несколько интервалов вперед или назад во времени.
  • SAMEPERIODLASTYEAR — возвращает таблицу, содержащую столбец с датами, сдвинутыми на один год назад от дат, указанных в столбце дат в текущем контексте фильтра.
  • Многие вспомогательные функции DAX для перемещения назад или вперед по конкретным периодам времени, каждая из которых возвращает таблицу дат. К этим вспомогательным функциям относятся NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR, а также PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER и PREVIOUSYEAR.

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

Revenue PY =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    RevenuePriorYear

Добавьте меру Revenue PY в матричный визуальный элемент. Обратите внимание, что он выдает результаты, аналогичные суммам доходов предыдущего года.

На изображении показан визуальный элемент матрицы с группировкой по годам и месяцам в строках и сводных данных Revenue, Revenue YTD и Revenue PY. Значения Revenue PY за месяц для FY2019 равны значениям Revenue за месяц для FY2018.

Далее вы измените меру, переименовав ее в Revenue YoY %, а затем обновив предложение RETURN для вычисления коэффициента изменения. Не забудьте изменить формат на процент с двумя десятичными знаками.

Revenue YoY % =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE(
        [Revenue] - RevenuePriorYear,
        RevenuePriorYear
    )

Обратите внимание, что мера Revenue YoY % создает коэффициент изменения по отношению к месячному доходу за предыдущий год. Например, показатель за июль 2018 г. представляет собой увеличение на 106,53 %, по сравнению с месячным доходом за июль предыдущего года, а показатель за ноябрь 2018 — уменьшение дохода на 24,22 %, по сравнению с месячным доходом за ноябрь предыдущего года.

На изображении показан визуальный элемент матрицы с группировкой по годам и месяцам в строках и сводных данных Revenue, Revenue YTD и Revenue YoY %. Значения Revenue YoY % за месяц для FY2019 — это значения, отформатированные в процентах.

Примечание

Мера Revenue YoY % демонстрирует правильное использование переменных DAX. Мера повышает удобочитаемость формулы и позволяет выполнять модульный тест части логики меры (путем возвращения значения переменной RevenuePriorYear). Кроме того, мера является оптимальной формулой, так как ей не нужно дважды извлекать значение дохода за прошлый год. После сохранения значения в переменной предложение RETURN использует это значение дважды.