Excel DAX - использование логики операций со временем

Вы узнали о мощной функции DAX Time Intelligence в главе - Understanding Time Intelligence. В этой главе вы узнаете, как использовать функции логики операций со временем DAX в различных сценариях.

Функции анализа времени DAX включают в себя -

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

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

  • Функции, извлекающие первую и последнюю дату указанного периода.

  • Функции, помогающие работать с начальным и конечным сальдо.

Расчет совокупных продаж

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

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - Оценивает выражение в последний день месяца в текущем контексте.

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - Оценивает выражение в первое число месяца в текущем контексте.

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - Оценивает выражение на последнюю дату квартала в текущем контексте.

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - Оценивает выражение на первую дату квартала в текущем контексте.

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) - Оценивает выражение в последний день года в текущем контексте.

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) - Оценивает выражение в первую дату года в текущем контексте.

Вы можете создать следующие вычисляемые поля для инвентаризации продукта в указанное время, используя следующие функции DAX:

Month Start Inventory Value: = OPENINGBALANCEMONTH ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Month End Inventory Value: = CLOSINGBALANCEMONTH ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Quarter Start Inventory Value: = OPENINGBALANCEQUARTER ( 
   SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Quarter End Inventory Value: = CLOSINGBALANCEQUARTER ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Year Start Inventory Value: = OPENINGBALANCEYEAR ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)
Year End Inventory Value: = CLOSINGBALANCEYEAR ( 
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Сравнение значений за разные периоды времени

По умолчанию DAX поддерживает периоды времени: месяцы, кварталы и годы.

Вы можете использовать следующие функции аналитики времени DAX для сравнения сумм за разные периоды времени.

  • PREVIOUSMONTH (<dates>) - Возвращает таблицу, содержащую столбец всех дат за предыдущий месяц на основе первой даты в столбце дат в текущем контексте.

  • PREVIOUSQUARTER (<dates>) - Возвращает таблицу, содержащую столбец всех дат за предыдущий квартал на основе первой даты в столбце дат в текущем контексте.

  • PREVIOUSYEAR (<dates>, <year_end_date>]) - Возвращает таблицу, содержащую столбец всех дат предыдущего года с учетом последней даты в столбце дат в текущем контексте.

Вы можете создать следующие вычисляемые поля для расчета суммы продаж в Западном регионе в указанные периоды времени для сравнения с помощью функций DAX:

Previous Month Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
)
Previous Quarter Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
)
Previous Year Sales: = CALCULATE ( 
   SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
)

Сравнение значений за параллельные периоды времени

Вы можете использовать функцию аналитики времени DAX PARALLELPERIOD для сравнения сумм за период, параллельный указанному периоду времени.

ПАРАЛЛЕЛЬНЫЙ ПЕРИОД (<даты>, <число_интервалов>, <интервал>)

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

Вы можете создать следующее вычисляемое поле для расчета продаж за предыдущий год в Западном регионе -

Previous Year Sales: = CALCULATE ( 
   SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year)
)

Расчет промежуточных итогов

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

  • TOTALMTD (<expression>,<dates>, [<filter>]) - Оценивает значение выражения за месяц до даты в текущем контексте.

  • TOTALQTD (<expression>,<dates>, <filter>]) - Оценивает значение выражения для дат в квартале до даты в текущем контексте.

  • TOTALYTD (<expression>,<dates>, [<filter>], [<year_end_date>]) - Оценивает значение выражения с начала года до даты в текущем контексте.

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

Текущая сумма за месяц: = TOTALMTD (SUM (West_Sales [SalesAmount]), DateTime [DateKey])

Промежуточная сумма за квартал: = TOTALQTD (SUM (WestSales [SalesAmount]), DateTime [DateKey])

Текущая сумма за год: = TOTALYTD (SUM (WestSales [SalesAmount]), DateTime [DateKey])

Расчет значения для настраиваемого диапазона дат

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

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) - Возвращает таблицу, содержащую столбец дат, который начинается с start_date и продолжается в течение указанного числа_интервалов.

DATESBETWEEN (<dates>, <start_date>, ) - Возвращает таблицу, содержащую столбец дат, который начинается с start_date и продолжается до end_date.

DATEADD (<dates>,<number_of_intervals>,<interval>) - Возвращает таблицу, содержащую столбец дат, сдвинутых вперед или назад во времени на указанное количество интервалов от дат в текущем контексте.

FIRSTDATE (<dates>) - Возвращает первую дату в текущем контексте для указанного столбца дат.

LASTDATE (<dates>) - Возвращает последнюю дату в текущем контексте для указанного столбца дат.

Вы можете создать следующие формулы DAX для расчета суммы продаж в Западном регионе за указанный диапазон дат с помощью функций DAX:

  • Формула DAX для расчета продаж за 15 дней до 17 июля 2016 г.

CALCULATE ( 
   SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day)
)
  • Формула DAX для создания вычисляемого поля, которое вычисляет продажи за первый квартал 2016 года.

= CALCULATE (
   SUM (WestSales[SalesAmount]),DATESBETWEEN (DateTime[DateKey], DATE (2016,1,1), DATE (2016,3,31))
)
  • Формула DAX для создания вычисляемого поля, которое получает первую дату совершения продажи в западном регионе для текущего контекста.

= FIRSTDATE (WestSales [SaleDateKey])
  • Формула DAX для создания вычисляемого поля, которое получает последнюю дату совершения продажи в западном регионе для текущего контекста.

= LASTDATE (WestSales [SaleDateKey])
  • Формула DAX для вычисления дат, которые на год раньше дат в текущем контексте.

= DATEADD (DateTime[DateKey],-1,year)