Excel DAX — Использование Time Intelligence

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

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

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

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

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

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

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

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

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

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

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

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

CLOSINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>]) — оценивает выражение в последнюю дату месяца в текущем контексте.

OPENINGBALANCEMONTH (<выражение>, <даты>, [<фильтр>]) — оценивает выражение в первую дату месяца в текущем контексте.

CLOSINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>]) — оценивает выражение на последнюю дату квартала в текущем контексте.

OPENINGBALANCEQUARTER (<выражение>, <даты>, [<фильтр>]) — оценивает выражение на первую дату квартала в текущем контексте.

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

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

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

 Стоимость инвентаризации на начало месяца: = OPENINGBALANCEMONTH ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

 Стоимость запасов на конец месяца: = CLOSINGBALANCEMONTH ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 )

 Значение инвентаризации на начало квартала: = OPENINGBALANCEQUARTER ( 
    SUMX ProductInventory, (ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

 Значение запаса на конец квартала: = CLOSINGBALANCEQUARTER ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

 Стоимость инвентаризации на начало года: = OPENINGBALANCEYEAR ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 )

 Стоимость запасов на конец года: = CLOSINGBALANCEYEAR ( 
    SUMX (ProductInventory, ProductInventory [UnitCost] * ProductInventory [UnitsBalance]), DateTime [DateKey]
 ) 

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

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

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

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

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

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

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

PREVIOUSQUARTER (<даты>) — возвращает таблицу, которая содержит столбец всех дат предыдущего квартала на основе первой даты в столбце дат в текущем контексте.

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

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

 Продажи за предыдущий месяц: = РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
 ) 

 Продажи за предыдущий квартал: = РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
 ) 

 Продажи за предыдущий год: = РАСЧЕТ ( 
    SUM (WestSales [SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
 ) 

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

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

PARALLELPERIOD (<даты>, <number_of_intervals>, <интервал>)

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

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

 Продажи за предыдущий год: = РАСЧЕТ ( 
    SUM (West_Sales [SalesAmount]), PARALLELPERIOD (DateTime [DateKey], - 1, год)
 ) 

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

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

• TOTALMTD (<выражение>, <даты>, [<фильтр>]) — оценивает значение выражения для месяца к дате в текущем контексте.

• TOTALQTD (<выражение>, <даты>, <фильтр>]) — оценивает значение выражения для дат в квартале от даты в текущем контексте.

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

TOTALMTD (<выражение>, <даты>, [<фильтр>]) — оценивает значение выражения для месяца к дате в текущем контексте.

TOTALQTD (<выражение>, <даты>, <фильтр>]) — оценивает значение выражения для дат в квартале от даты в текущем контексте.

TOTALYTD (<expression>, <date>, [<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 (<date>, <start_date>, <number_of_intervals>, <interval>) — возвращает таблицу, которая содержит столбец дат, который начинается с start_date и продолжается для указанного number_of_intervals.

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

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

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

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

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

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

Формула DAX для создания вычисляемого поля, которое рассчитывает продажи в первом квартале 2016 года.

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

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

Формула DAX для расчета дат за один год до дат в текущем контексте.