Великолепные функции работы со временем в надстройке PowerPivot
В предыдущей статье при использовании функции Calculate активно применялись фильтры. Например, в последнем рассмотренном примере с помощью фильтра среди общего объема продаж выделялись продажи торгового представителя по имени Эмбер. Также выполнялась фильтрация дат, соответствующих функции определения даты.
В PowerPivot используются 34 функции работы со временем. Предположим, что нужно вычислить текущий итог по продажам с начала текущего месяца. Для этого можно воспользоваться функцией Calculate и определить фильтр DatesMTD (Sis [Date] ). Если использовать псевдокод, ваша фраза будет звучать следующим образом: «Я хочу воспользоваться формулой, которая складывает результаты продаж по датам с начала текущего месяца, но только для торговых представителей, соответствующих текущему столбцу». Для суммирования всех продаж воспользуйтесь формулой =Calculate(Sum(Sis[Sales])
.
Для отбора дат, которые попадают в диапазон с начала текущего месяца, воспользуйтесь формулой DatesMTD(Sis[Date])
. Для отображения результатов продаж только для определенных торговых представителей воспользуйтесь формулой AllExcept(Sis,Sis[Rep])
. На рис. 10.41 показана завершенная формула, которая имеет такой вид: =Calculate(Sum(sis[Sales]);DatesMTD(Sis[Date]);AllExcept(Sis,Sis[Rep]))
.

Рис. 10.41. Величины продаж с начала текущего месяца для каждого торгового представителя
Обратите внимание: поскольку Дэйл не работал 4 июня, на эту дату отсутствует сумма продаж с начала текущего месяца. Если у вас большой опыт работы со сводными таблицами, то бы уже знаете, что ячейки, в которых отсутствуют значения, отображаются пустыми. В результате соответствующего изменения параметров сводной таблицы в подобных ячейках отображаются нули. Нечто подобное наблюдается в данном случае. Поскольку отсутствуют сведения о продажах Дэйла за 4 июня, на эту дату не вычисляется ни одна из мер DAX. Чтобы убрать пустые места, вернитесь к исходным данным и создайте записи для каждого торгового представителя, имеющего нулевые объемы продаж в определенные дни.
Обратите внимание на меру, которая может ссылаться на другую меру. Например, для получения доли торгового представителя от объема продаж с начала текущего месяца (This Rep’s Percentage of MTD Sales) от всех продаж с начала текущего месяца (All MTD Sales) сначала создайте меру, вычисляющую продажи с начала месяца для торгового представителя (MTDThisRep), как отмечалось выше. Затем создайте меру, которая подсчитывает продажи с начала месяца для всех торговых представителей (MTD All Reps). Для выполнения этой задачи воспользуйтесь такой формулой: =Calculate(Sum(Sls[Sales]);Dates MTD(Sis[Date]);All (Sis))
. Теперь для получения требуемого отношения создайте формулу =Sls[MTDThisRep]/Sis[MTD All Reps])
, показанную на рис. 10.42.

Рис. 10.42. Воспользуйтесь ранее определенными мерами для упрощения создания другой меры
В базовых таблицах может применяться технология фильтрации, основанная, например, на функциях All() и AllExcept(). Можно относиться по-разному к фильтру в связанной таблице в процессе выполнения вычислений. Конечно, лучше, чтобы поля фильтра находились в базовой таблице. Один из вариантов подобной рабочей среды заключается в том, чтобы в окне PowerPivot была определена функция =Related(), которая копирует поле из связанной таблицы в базовую.
Ниже приводится полный список функций работы со временем надстройки PowerPivot.
- СLOSINGBALANCEMONTH(<выражение>, <даты>, <фильтр>)
- CLOSINGBALANCEQUARTER(<выражение>, <даты>, <фильтр>)
- CLOSINGBALANCEYEAR(<выражение>, <даты>, <фильтр>)
- DATEADD (<столбец_даты>, <число__интервалов>, <интервал>)
- DATESBETWEEN(<столбец>, <начальная_дата>, <конечная_дата>)
- DATESINPERIOD (<столбец_даты>, <начальная_дата>, <число_интервадов>, <интервалы>)
- DATESMTD(<столбец_даты>)
- DATESQTD (<столбец_даты>)
- DATESYTD (<столбец_даты>, [<конечная_дата_текущего_года>])
- ENDOFMONTH(<столбец_даты>)
- ENDQFQUARTER(<столбец_даты>)
- ENDOFYEAR(<столбец_даты>)
- FIRSTDATE (<столбец_даты>)
- LASTDATE (<столбец_даты>)
- LASTNONBLANK (<столбец_даты>, <выражение>)
- NEXTDAY(<столбец_даты>)
- NEXTMONTH(<столбец_даты>)
- NEXTQUARTER(<столбец_даты>)
- NEXTYEAR (<столбец_даты>, [<конечная_дата_текущего_года>))
- OPENINGBALANCEMONTH(<выражением, <даты>, <фильтр>)
- OPENINGBALANCEQUARTER(<выражение>, <даты>, <фильтр>)
- OPENINGBALANCEYEAR(<выражение>, <даты>, <фильтр>)
- PARALLELPERIOD(<столбец_даты>, <число_интервалов>, <интервалы>)
- PREVIOUSDAY(<столбец_даты>)
- PREVI0USMONTH(<столбец_даты>)
- PREVIOUSQUARTER(<столбец_даты>)
- PREVIOUSYEAR(<столбец_даты>)
- SAMEPERIODLASTYEAR(<даты>)
- STARTOFMONTH(<столбец_даты>)
- STARTOFQUARTER(<столбец_даты>)
- STARTOFYEAR(<столбец_даты>),<конечная_дата_текущего_года>])
- TotalMTD(<выражение>, <даты>, <фильтр>)
- TotalQTD(<выражение>, <даты>, <фильтр>)
- TotalYTD(<выражение>, <даты>,<фильтр>)
Проверьте необязательный параметр Year Ending date (Дата завершения года) и другие аргументы функции DatesYTD. Благодаря этим параметрам можно производить расчеты по финансовым годам, отличающимся от календарных, которые завершаются 31 декабря.
А теперь для комплектности рассмотрим остальные функции, поддерживаемые DAX.
- BLANK()
- RELATED(<столбец>)
- ALL(<таблица_или_столбец>)
- DISTINCT(<столбец>)
- EARLIER (<столбец>, <число)
- EARLIEST(<таблица_или_столбец>)
- VALUES (<столбец>)
- ALLEXCEPT(<таблица>,<столбец1>, <столбец2>,..)
- CALCULATE(<выражение>,<фильтр1>,<фильтр2>, …)
- CALCULATETABLE(<выражение>,<фильтр1>,<фильтр2>, …)
- FILTER(<таблица>, <фильтр>)
- RELATEDTABLE(<таблица>)
- ALLNONBLANKROW(<столбец>)
- FIRSTNONBLANK(<столбец>, <выражение>)
- AVERAGEX(<таблица>,<выражение>)
- COUNTАХ(<таблица>,<выражение>)
- COUNTROWS(<выражение>)
- COUNTX(<таблица>, <выражение>)
- MAXX(<таблица>,<выражение>)
- MINX(<таблица>, <выражение>)
- SUMX(<таблица>, <выражение>)