Великолепные функции работы со временем в надстройке 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. Величины продаж с начала текущего месяца для каждого торгового представителя

Рис. 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. Воспользуйтесь ранее определенными мерами для упрощения создания другой меры

Рис. 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(<таблица>, <выражение>)
Top