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)