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

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

Для работы с функциями аналитики времени в модель данных должна быть включена таблица дат. Таблица дат должна включать столбец с одной строкой для каждого дня каждого года, включаемой в данные. Этот столбец считается столбцом даты (хотя его можно назвать как угодно). Многим функциям аналитики времени требуется столбец дат для вычисления в соответствии с датами, выбранными в отчете в качестве полей. Например, если есть мера, которая вычисляет баланс по кварталам за конец с помощью функции CLOSINGBALANCEQTR, power Pivot сможет узнать, когда на самом деле заканчивается год, он должен ссылаться на столбец дат в таблице дат, чтобы знать, когда начинается и заканчивается квартал. Чтобы узнать больше о таблицах дат, изучите таблицы дат и создайте их в Power Pivot в Excel.

Функции

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

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

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

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

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

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

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

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

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

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

  • DATEADD (Date_Column, Number_of_Intervals, интервал)

  • DATESBETWEEN (Date_Column, Start_Date, End_Date)

  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Interval)

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

  • Назад на два года

  • Вернуться на один месяц назад

  • Перейти на три квартала вперед

  • Вернуться на 14 дней назад

  • Перейти вперед на 28 дней

В каждом случае нужно указать только интервал и нужно, сколько интервалов нужно сдвинуть. Положительный интервал будет со временем перемещаться вперед, а отрицательный интервал назад во времени. Интервал определяется ключевым словом дня, МЕСЯЦА, КВАРТАЛА или ГОДА. Эти ключевые слова не являются строками, поэтому они не должны быть в кавычках.

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

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

= TOTALMTD (выражение, Date_Column [, SetFilter])

в точности то же, что и:

= CALCULATE (выражение, DATESMTD (Date_Column)[, SetFilter])

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

  • TOTALMTD (выражение, Date_Column [, SetFilter])

  • TOTALQTD (выражение, Date_Column [, SetFilter])

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

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

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

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

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

Справка: справочник по функции DAXOffice.com

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

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×