Часовий аналіз у надбудові Power Pivot для Excel

Застосовується до
Excel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

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

Щоб працювати з функціями часового аналізу, потрібно включити таблицю дат до моделі даних. Таблиця дат має містити стовпець з одним рядком для кожного дня кожного року, включеного до даних. Цей стовпець має назву стовпця дати (хоча його можна назвати будь-що). Для багатьох функцій часового аналізу потрібен стовпець дат, щоб обчислювати дані відповідно до дат, вибраних полями у звіті. Наприклад, якщо у вас є міра, яка обчислює сальдо за підсумками кварталу за допомогою функції CLOSINGBALANCEQTR, щоб надбудова Power Pivot могла дізнатися, коли насправді кінець кварталу, має посилатися на стовпець дат у таблиці дат, щоб дізнатися, коли починається та закінчується квартал. Докладні відомості про таблиці дат див. в статті "Створення таблиць дат у надбудові Power Pivot для Excel".

Функції

Функції, які повертають одну дату

Функції цієї категорії повертають одну дату. Результат можна використовувати як аргументи для інших функцій.

Перші дві функції в цій категорії повертають першу або останню дату в Date_Column в поточному контексті. Це зручно, якщо потрібно знайти першу або останню дату, коли відбулася транзакція певного типу. Ці функції мають лише один аргумент – ім'я стовпця дати в таблиці дат.

Наступні дві функції в цій категорії знаходять першу або останню дату (або будь-яке інше значення стовпця), для якого вираз має непусте значення. Це найчастіше використовується в ситуаціях, наприклад, для інвентаризації, коли потрібно отримати суму останнього інвентарного запасу, але невідомо, коли було проведено останній інвентарний список.

Ще шість функцій, які повертають одну дату, – це функції, які повертають перший або останній день місяця, кварталу або року в поточному контексті обчислення.

Функції, які повертають таблицю дат

Існує шістнадцять функцій часового аналізу, які повертають таблицю дат. Найчастіше ці функції використовуються як аргумент SetFilter для функції CALCULATE . Як і всі функції часового аналізу в DAX, кожна функція приймає стовпець дат як один зі своїх аргументів.

Перші вісім функцій у цій категорії починаються зі стовпця дат у поточному контексті. Наприклад, якщо у зведеній таблиці використовується міра, у підписах стовпців або рядків може бути вказано місяць або рік. Загальний ефект полягає в тому, що стовпець дат фільтрується так, щоб включити лише дати в поточному контексті. Виходячи з цього поточного контексту, ці вісім функцій обчислюють попередній (або наступний) день, місяць, квартал або рік і повертають ці дати у вигляді таблиці з одним стовпцем. Функції "previous" працюють у зворотному напрямку від першої дати в поточному контексті, а функції "наступної" переміщуються вперед від останньої дати в поточному контексті.

Наступні чотири функції в цій категорії схожі, але замість попереднього (або наступного) періоду вони обчислюють набір дат у періоді, тобто "з початку місяця" (або з початку кварталу, або з початку року, або за аналогічний період попереднього року). Усі ці функції виконують обчислення, використовуючи останню дату в поточному контексті. Зауважте, що для функції 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 (вираз, 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

Довідка: довідник із функцій DAX на Office.com

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