Сравнение любых произвольных дат и/или товаров в Power BI

При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.

Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?

Решение такой задачи весьма несложно, хотя и слегка нетривиально.

Только сразу хочу уточнить, на всякий случай, что я тут предполагаю, что вы уже имеете базовые навыки работы в Power BI, т.е. умеете загружать туда данные, писать хотя бы простые меры и создавать вычисляемые столбцы на языке DAX и уместили в голове идею использования связей между таблицами в Модели Данных вместо ВПР. Если этого ещё нет, то настоятельно рекомендую сначала пробежаться по основам, а потом уже продолжать дальше.

Исходные данные

В качестве примера возьмем простую модель, состоящую из 3 таблиц: таблицы продаж Sales, справочника по товарам Products и календарной таблицы дат Calendar.

В отчёте для товаров из Products и лет из Calendar добавим срезы для фильтрации и разместим рядом карточку с результатом простой меры, суммирующей проданное количество товаров:

Дублируем справочники с неактивными связями

Чтобы иметь возможность независимо фильтровать другие годы и товары на ещё 2 дополнительных срезах, нужно будет создать копии наших исходных таблиц-справочников: Products и Calendar. Для этого на вкладке Главная жмём на кнопку Создать таблицу (Home - Create Table) и используем очень простую конструкцию, чтобы создать клоны исходных таблиц:

Calendar2 = 'Calendar'

и

Products2 = Products

После этого в Модели данных связываем наши дубликаты-справочники с таблицей продаж Sales по тем же столбцам Артикул и Дата продажи, что и ранее.

И вот тут ключевой момент - две созданные только что связи нужно сделать неактивными (правой кнопкой мыши по связи - снять флажок Активная связь). В противном случае фильтры от первой пары срезов сложатся (логическое AND) с фильтрами из второй пары срезов и мы получим пустоту (если выбраны разные годы или товары). Нам же нужно фильтрацию справочников-клонов сделать независимой, поэтому мы и деактивируем их связи с таблицей продаж.

Создаем меру для второй выборки

Осталось добавить в отчёт срезы по году и товару из клонов-справочников Calendar2 и Products2 и написать меру, которая будет считать продажи только для них:

Логика тут простая:

• снаружи всё заворачивается в функцию CALCULATE, которая умеет менять контекст вычислений
• первый аргумент этой функции - это мера для расчёта проданных товаров [Продажи1], которую мы хотим вычислить
• вторым аргументом ALL(Sales) мы снимаем все фильтры с таблицы продаж, наложенные первой парой срезов
• третий и четвертый аргументы оживляют неактивные связи на время вычисления текущей меры с помощью специальных функций-модификаторов USERELATIONSHIP

Для полноты картины можно сделать ещё и простую меру, вычисляющую процентную разницу между Продажами1 и Продажами2:

Разница в % = DIVIDE([Продажи2]-[Продажи1] , [Продажи1])

Вот и всё, собственно.

Само собой, подобным образом можно сравнивать абсолютно всё, что угодно и в любых количествах - метод универсальный.

Ссылки по теме

• Основы работы в Microsoft Power BI
• Хитрости сортировки в Microsoft Power BI
• Три способа сравнить год-к-году в сводной таблице Excel