Логика операций со временем в PowerPivot для Excel

Применяется к
Excel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Выражения анализа данных (DAX) имеют 35 функций, предназначенных специально для агрегирования и сравнения данных с течением времени. В отличие от функций даты и времени DAX, функции аналитики времени не имеют ничего общего с функциями Excel. Это связано с тем, что функции анализа времени работают с данными, которые постоянно изменяются в зависимости от контекста, выбранного в сводных таблицах и визуализациях Power View.

Для работы с функциями логики ко времени необходимо, чтобы в модель данных была включена таблица дат. Таблица дат должна содержать столбец с одной строкой для каждого дня каждого года, включенного в данные. Этот столбец считается столбцом "Дата" (хотя его можно назвать как угодно). Многим функциям логики ко времени требуется столбец даты для вычислений на основании дат, выбранных в качестве полей отчета. Например, если у вас есть мера, которая вычисляет сальдо на конец квартала с помощью функции ЗАКРЫТИЕBALANCEQTR, чтобы приложение Power Pivot могло узнать, когда на самом деле заканчивается квартал, необходимо сослаться на столбец даты в таблице дат, чтобы узнать дату начала и окончания квартала. Дополнительные сведения о таблицах дат см. в статье "Понимание и создание таблиц дат в Power Pivot в Excel".

Функции

Функции, возвращающие одну дату

Функции этой категории возвращают одну дату. Затем результат можно использовать в качестве аргументов для других функций.

Первые две функции этой категории возвращают первую или последнюю дату в Date_Column в текущем контексте. Это может быть полезно, если требуется найти первую или последнюю дату проведения транзакции определенного типа. Эти функции принимают только один аргумент — имя столбца дат в таблице дат.

Следующие две функции этой категории находят первую или последнюю дату (или любое другое значение столбца), в которых выражение имеет непустое значение. Чаще всего это используется в таких ситуациях, как инвентаризация, когда вы хотите получить последнее количество запасов, но не знаете, когда был проведен последний запас.

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

Функции, возвращающие таблицу дат

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

Первые восемь функций в этой категории начинаются со столбца даты в текущем контексте. Например, если в сводной таблице используется мера, в заголовках столбцов или строк может быть месяц или год. Итоговым эффектом является то, что столбец дат фильтруется, чтобы включить только даты для текущего контекста. Отталкиваясь от текущего контекста, эти восемь функций вычисляют предыдущий (или следующий) день, месяц, квартал или год и возвращают эти даты в виде таблицы с одним столбцом. Функции "previous" работают в обратном направлении от первой даты в текущем контексте, а функции "next" — от последней даты в текущем контексте.

Следующие четыре функции в этой категории аналогичны, но вместо вычисления предыдущего (или следующего) периода они вычисляют набор дат в периоде, который является "с начала месяца" (или с начала квартала, или с начала года, или в тот же период предыдущего года). Все эти функции выполняют вычисления, используя последнюю дату в текущем контексте. Заметьте, что функция SAMEPERIODLASTYEAR требует, чтобы текущий контекст содержал непрерывный набор дат. Если текущий контекст не является непрерывным набором дат, то функция SAMEPERIODLASTYEAR вернет ошибку.

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

  • DATEADD (Date_Column, Number_of_Intervals, интервал)
  • DATESBETWEEN (Date_Column, Start_Date, End_Date)
  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, интервал)

Функция DATESBETWEEN вычисляет набор дат между указанной датой начала и датой окончания. Оставшиеся три функции смещают некоторое количество временных интервалов от текущего контекста. Интервал может быть день, месяц, квартал или год. Эти функции позволяют легко сместить интервал времени для расчета с помощью любого из следующих способов:

  • Назад на два года назад
  • Назад на один месяц
  • Вперед на три четверти
  • Назад на 14 дней
  • Вперед на 28 дней

В каждом случае нужно только указать, какой интервал и сколько из этих интервалов нужно сдвинуть. Положительный интервал будет двигаться вперед во времени, а отрицательный — назад во времени. Интервал задается ключевым словом DAY, MONTH, QUARTER или YEAR. Эти ключевые слова не являются строками, поэтому их не следует заключать в кавычки.

Функции, оценивающие выражения за определенный период времени

Эта категория функций оценивает выражение за указанный период времени. Того же результата можно добиться с помощью функции CALCULATE и других функций логики ко времени. Например:

= TOTALMTD (Expression, Date_Column [, SetFilter])

точно такое же:

= CALCULATE (Expression, DATESMTD (Date_Column)[, SetFilter])

Однако эти функции анализа времени удобнее использовать, когда они хорошо подходят для задачи, которую необходимо решить:

  • TOTALMTD (выражение, Date_Column [, SetFilter])
  • TOTALQTD (выражение, Date_Column [, SetFilter])
  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) *

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

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

Дополнительные ресурсы

Статьи: Общие сведения и создание таблиц дат в Power Pivot в Excel

Справочник: Справочник по функциям DAX в Office.com

Примеры: моделирование и анализ данных о прибылях и убытках с помощью Microsoft PowerPivot в Excel