Скользящее среднее в Power BI
Скользящее среднее (Simple Moving Average = SMA) - очень популярный метод анализа данных, используемый во множестве разных областей, включая финансовую аналитику, технический анализ в трейдинге, прогнозирование погоды, обработку сигналов и т.д.
Суть метода очень проста: для каждого значения временного ряда мы берём данные за N предыдущих периодов и их усредняем. Получается эдакое "окно" размером N элементов, скользящее по исходным данным - отсюда и название этого подхода. Само-собой, чем больше размер окна, тем сильнее получается усреднение и сглаживание.
В реальных задачах этот метод используют, например, чтобы:
- Уменьшить шум (болтанку), исключив краткосрочные колебания, чтобы лучше видеть глобальное поведение в наших данных.
- Поймать момент перелома, когда восходящий тренд сменяется нисходящим.
- Выполнить простейший краткосрочный прогноз
Применительно к Microsoft Excel формула для вычисления скользящего среднего можно использовать функцию СРЗНАЧЕСЛИМН (AVERAGEIFS), которая будет усреднять данные из прошлого, попадающие в окно заданного размера. Чтобы на первой ячейке (для которой нет прошлого) не возникала ошибка, формулу можно дополнительно завернуть в функцию перехвата ошибок ЕСЛИОШИБКА (IFERROR):
Как же реализовать подобную логику в Power BI?
Вариант 1. Простое скользящее среднее (SMA) по дням
Предположим, что у нас есть та же таблица с данными о продажах (назовем её fSales), загруженная в Power BI:
Для вычислений, связанных с датой-временем в Power BI есть множество мощных и удобных встроенных функций (Time Intelligence Functions), но все они требуют обязательного наличия в модели таблицы дат - календарного справочника, состоящего из основного столбца с датами без разрывов и повторов и вспомогательных столбцов с атрибутами каждой даты. Предположим, что у нас эта таблица называется dCalendar выглядит так:
В Модели данных обе таблицы, естественно, связаны по полю даты:
Само собой, все вычисления мы будем делать с помощью мер - формул на языке DAX, которые потом будет работать в любой визуализации (графике, таблице...) нашего дашборда Power BI. Чтобы создать меру, щёлкнем правой кнопкой мыши по таблице, где мы хотим её хранить (например, по Sales) и выберем команду Создать меру (New measure).
Для упрощения будущих расчётов давайте сначала создадим простейшую меру, которая будет подсчитывать общую выручку:
Общая выручка = SUM(fSales[Выручка])
И построим в дашборде таблицу, куда закинем поле ГодМесяц из календарной таблицы и только что созданную меру итоговой выручки:
После этого можно приступать к написанию меры скользящего среднего (например, за 3 последних дня). Сама по себе формула не очень длинная, но чтобы была понятна логика вычислений, начнём немного издалека.
Первая наша задача - определить текущую дату для каждого периода в отчёте, от которой будет откладываться в прошлое наш 3-дневный интервал. Для этого просится простая, вроде бы, формула:
SMA = dCalendar[Date]
Но после её ввода и нажатия на Enter мы получим ошибку:
В чём же дело?
Здесь нужно вспомнить, что любая мера в Power BI вычисляется, как говорят, в контексте фильтров отчёта, т.е. только после применения всех фильтров, которые есть у нас в дашборде. Так, например, в строке "2020-01" наша мера отфильтрует и вернёт все январские даты 2020 года, в строке напротив "2020-02" - все 29 дней февраля и т.д. А вывести несколько значений в одну ячейку таблицы Power BI не может (в отличие от того же Power Query, например - там это вполне ОК).
И даже если мы вместо месяцев положим в нашу таблицу дни (т.е. столбец [Дата]) - ошибка не исчезнет. Ведь в общем случае, мы можем захотеть строить наше скользящее среднее с детализацией по неделям, месяцам, годам, т.е. для каждой строки таблицы в нашем дашборде будут отфильтровываться не одна, а несколько дат.
Так что для корректной обработки нам нужно гарантированно получить одиночную дату, от которой затем будет откладываться в прошлое наше скользящее окно. Решается эта проблема просто - обычно для этого используют функции MAX или LASTDATE, чтобы извлекать последний день каждого периода для каждой строки в отчёте:
SMA = LASTDATE(dCalendar[Дата])
В этом можно легко убедиться, если закинуть нашу ещё не завершенную меру в таблицу дашборда:
Следующий шаг - отфильтровать все даты, отстоящие от текущей даты на X периодов в прошлое. Для этого можно воспользоваться готовой функцией языка DAX, которая называется DATESINPERIOD:
SMA =
DATESINPERIOD ( dCalendar[Дата], LASTDATE ( dClendar[Дата] ), -3, DAY )
Первый аргумент здесь - это столбец со всеми возможными датами в нашей календарной таблице, второй - последняя дата отфильтрованного периода, от которой затем откладывается N заданных периодов (3 и 4-й аргументы, т.е. 3 дня в прошлое), чтобы сформировать наше скользящее окно.
Визуально убедиться, что формула работает корректно, с ходу не получится, потому что (опять же) вывести три даты в одну ячейку Power BI не умеет. Однако, можно использовать простой трюк - завернуть нашу формулу в функцию CONCATENATEX, которая сцепляет все значения из указанного столбца таблицы через заданный символ-разделитель:
Окно дат =
CONCATENATEX (
DATESINPERIOD ( dCalendar[Дата], LASTDATE ( dCalendar[Дата] ), -3, DAY ),
[Дата],
" ; "
)
Теперь хорошо видно, что для каждой даты в отчёте мы отбираем 3 предыдущих дня:
Ну и, наконец, последний шаг - посчитать средние продажи за все дни в окне. Для этого используем функцию CALCULATE:
SMA =
CALCULATE (
AVERAGE ( fSales[Выручка] ),
DATESINPERIOD ( dCalendar[Дата], LASTDATE ( dCalendar[Дата] ), -3, DAY )
)
Первый её аргумент - это формула или мера, которую нужно вычислить. В нашем случае - это простое среднее арифметическое по полю выручки.
Второй аргумент функции CALCULATE - это по-сути фильтр, задающий принцип отбора строк (контекст) перед вычислением первого аргумента. Это может быть либо явное логическое условие (типа выручка > 100), либо таблица, значения которой используются как критерии, как в нашем случае.
Таким образом при вычислении, например, скользящего среднего за 5 января, второй аргумент CALCULATE отфильтрует диапазон 3-5 января в календарной таблице, а затем эти фильтры распространятся по связи в таблицу продаж fSales, где останутся только сделки за эти 3 дня, на которых в свою очередь, и будет вычисляться AVERAGE по выручке, чтобы получить нужное нам скользящее среднее.
Для наглядности стоит построить простую визуализацию-график, где даты из календарной таблицы будут по оси X, а выручка и мера скользящего среднего по оси Y:. Если задать размер окна побольше (например, 30 дней), то хорошо будет заметен эффект сглаживания:
Чтобы удобнее было подстраивать под себя шаг, его можно ввести в формулу как параметр. Для этого на вкладке Моделирование выберем Создание параметра - Числовой диапазон (Modelling - New parameter - Number range) и зададим интересующий нас диапазон дней для окна усреднения - например от 1 до 100 дней:
После нажатия на кнопку Создать Power BI создаст новую таблицу с числовой последовательностью от 1 до 100 с помощью функции GENERATESERIES:
... а также добавит на страницу ползунок, привязанный к этой таблице и вспомогательную одноименную меру Значение Окно SMA, которая вытаскивает заданное ползунком значение.
После этого можно вернуться к нашей мере скользящего среднего и подставить туда имя этой меры вместо жёстко прописанного количества дней:
SMA =
CALCULATE (
AVERAGE ( fSales[Выручка] ),
DATESINPERIOD ( dCalendar[Дата], LASTDATE ( dCalendar[Дата] ), -[Значение Окно SMA], DAY )
)
Теперь можно задавать размер окна и степень сглаживания, двигая ползунок мышью - легко и удобно.
Вариант 2. Скользящее среднее по месяцам
Описанный выше подход отлично работает до тех пор, пока вы не захотите построить скользящее среднее не за N дней, а, например, за N месяцев (недель, кварталов, лет). Если грануляция (детализация) исходных данных (у нас это дни) не совпадает с грануляцией вычисляемого скользящего среднего (месяцы), то получается ерунда - у нас будут вычисляются среднедневные продажи за последние 3 месяца, а не среднемесячные за этот же период.
Для решения такой задачи потребуется мера похитрее:
SMA 3 Months =
CALCULATE (
AVERAGEX ( VALUES ( dCalendar[ГодМесяц] ), [Общая выручка] ),
DATESINPERIOD ( dCalendar[Дата], LASTDATE ( dCalendar[Дата] ), -3, MONTH )
)
Здесь:
- Сначала мы с помощью функции VALUES извлекаем из календарной таблицы dCalendar список уникальных значений по столбцу [Год-Месяц].
- Затем внутри функции AVERAGEX мы проходим по этому списку и вычисляем для каждого месяца каждого года меру общей выручки, которые затем усредняем, т.е. фактически считаем среднемесячную выручку.
- Всё вышеперечисленное вычисляется внутри функции CALCULATE, вторым аргументом которой идёт уже знакомая нам конструкция отбора 3 последних месяцев в таблице дат.
Другими словами мы тут сначала суммируем выручку внутри месяцев, т.е. искусственно укрупняем грануляцию, и только потом считаем среднемесячную выручку за 3 последних месяца для скользящего среднего.
Вместо функции VALUES можно использовать функции DISTINCT или SUMMARIZE - идея плюс-минус та же.
Вариант 3. Визуальные вычисления для ленивых
Если формула из предыдущего способа вгоняет вас в тоску, то я вас прекрасно понимаю - DAX бывает той еще мозголомкой. Именно поэтому в февральском обновлении Power BI Desktop Microsoft добавила новый инструмент для тех, у кого нет времени или сил углубляться в дебри DAX, а отчёт был нужен ещё вчера. Этот инструмент называется Визуальные вычисления (Visual calculations).
Чтобы его включить вам нужна февральская (или новее) версия Power BI Desktop, где в меню Файл - Параметры и настройки - Параметры в разделе Предварительные версии возможностей необходимо включить флажок Визуальные вычисления (File - Options & settings - Options - Preview features - Visual calculations).
После перезапуска Power BI на главной вкладке появится новая кнопка:
Теперь можно выделить таблицу, где вы хотите создать скользящее среднее, и добавить в неё новое вычисление. В нижней части окна отобразится новая панель, где можно с помощью кнопки fx выбрать нужную формулу из выпадающего списка:
А затем заполнить два её аргумента - меру, которую мы хотим подсчитать и размер окна усреднения:
После нажатия на символ галочки в строке формул и возвращения к отчету кнопкой в левом верхнем углу, мы получим ещё один столбец со скользящим средним, но гораздо проще, чем в предыдущем случае.
Если на этом моменте у вас в голове возникла крамольно-радостная мысль "а зачем мне теперь этот DAX?", то, к сожалению, буду вынужден вас слегка охладить - визуальные вычисления имеют несколько серьезных недостатков по сравнению с классическими мерами, например:
- в визуальных вычислениях можно использовать только ограниченный набор функций (а не все 300+ функций DAX)
- визуальные вычисления совместимы не со всеми визуализациями
- визуальные вычисления не видят ничего за пределами той визуализации, в которой используются
- в них нельзя использовать параметры (т.е. привязать ползунок к шагу скользящего среднего не получится, например)
- ... и т.д.
Но если вам нужно просто и быстро посчитать навскидку какие-то типовые KPI, то визуальные вычисления - это неплохой вариант, однозначно.
Ссылки по теме
- Основы работы в Microsoft Power BI
- Сравнение любых произвольных дат, товаров, объектов в Power BI
- Хитрости сортировки данных в Power BI
Будем надеяться, что они это еще допилят