Добрый день, уважаемые читатели!
Как и обещал, начинаю делиться с вами полезными случаями из практики.
Довольно часто возникает потребность в том, чтобы при выборе даты в срезе, выводились данные, которые являются "активными" на эту дату. То есть дата среза должна быть между датой 1 (начала действия) и датой 2 (окончания).
Условия задачи:
Дано: список контрактов, у каждого есть дата начала и дата окончания. В дашборд вынесен срез (slicer) по дате, который взят из справочника и фильтрует разные таблицы и графики на листе.
Задача: Вывести в таблицу все активные контракты за выбранный месяц. То есть, если в срезе мы выбираем сентябрь 2020, то в таблице должны отразиться все контракты, которые были открыты до сентября (включительно) и закрыты после сентября.
Решить задачу нужно средствами DAX.
На первый взгляд, задача простая. Вы можете установить связь между календарем и таблицей "Контракты" по дате закрытия. Однако в этом случае, вы получите только те контракты, который были закрыты в выбранном месяце. Это не то, что нам нужно.
Есть еще вариант - установить 2 среза: один по дате открытия контракта, второй по дате закрытия. Но, во первых, это не удобно, во вторых это дополнительный срез, которые будет "болтаться" на листе. Напомню, что по условиям задачи у нас есть другие данные, на которые влияет срез по дате.
Давайте рассуждать дальше. Вот если бы нам знать какой месяц выбран в срезе, мы бы могли сравнить его с датой начала и датой окончания и понять активен ли контракт в этом месяце. Нет проблем, воспользуемся функцией SELECTEDVALUE. Эта функция позволит нам "посмотреть" на отфильтрованную таблицу после выбора значения в срезе.
У функции SELECTEDVALUE есть пара особенностей:
1) В том виде, в котором я ее применил, она не будет работать, если вы выбираете несколько месяцев. Это также решаемая проблема, но не в рамках данной статьи. В данном случае, мы настраиваем срез так, чтобы можно было выбрать только один месяц.
2) SELECTEDVALUE не работает в столбце таблицы. Нам ничего не мешает ее туда вписать, но она не будет показывать выбранное значение среза. Это связано с особенностями обновления данных, формулы в таблицах не пересчитываются при изменении значений среза. Она работает только в мере.
Второй пункт создает нам проблемы, так как было бы удобно создать дополнительный столбец и сравнить каждую строчку таблицы с выбранным значением среза. С другой стороны мера не умеет работать со столбцом без функций - агрегаторов. Значит будем использовать агрегатор, в качестве функции агрегатора выберем SUMX. Особенность функции SUMX заключается в том, что она считает таблицу построчно, запоминает посчитанное для каждой строки значение в виртуальной памяти, а затем суммирует эти значения. Ключевое слово - построчно.
Но сначала нужно доработать календарь. Добавим в него столбец с концом месяца. Далее будем сравнивать даты начала и окончания контракта с концом месяца. Это позволит нам включить в таблицу те контракты, которые начались до 30/31 числа выбранного месяца и закончились после 30/31 (то есть уже в след. месяце). Сделать это очень просто, достаточно использовать функцию ENDOFMONTH
Далее обернем SELECTEDVALUE в SUMX. Пишем формулу:
Попробую поэтапно объяснить написанную формулу:
1 Функция FILTER фильтрует таблицу "Contracts" по указанным правилам, а именно: дата открытия контракта должна быть меньше или равно концу месяца, который выбрали в срезе + дата закрытия должна быть больше чем конец месяца, который выбрали в срезе.
2 Функция SUMX в качестве первого параметра принимает таблицу, которая уже отфильтрована функцией FILTER. В качестве второго параметра можно указать любое числовое значение. Я сделал дополнительный столбец с номером месяца. Логика такая: SUMX получает нужную нам таблицу с контрактами, которые активны в выбранный месяц, далее SUMX проходит по каждой строке таблицы, запоминает значение NUMBER и в итоге суммирует все значения.
3 Функция IF тут нужна, чтобы разделить активные и неактивные контракты. Я это сделал через сравнение SUMX с нулем. Таким образом, если вы выберете на срезе месяц, в котором нет активных контрактов, функция FILTER вернет пустую таблицу, SUMX будет нечего суммировать и она вернет ноль, а IF вернет "no"
Да, забыл сказать, связь между таблицей контрактов и календарем нужно удалить.
Ну и заключительная часть нашей работы: выносим нашу меру в фильтры отчета, выбираем "содержит" и пишем "Yes".
Таблица будет фильтроваться только по активным контрактам.
Я привел простой пример, конечно в реальности все немного сложнее, начиная от необходимости отслеживая года и заканчивая выбором нескольких месяцев. Но я надеюсь, вы поняли логику работы с данными среза для фильтрации активных контрактов.
Если вам интересен Power BI, подписывайтесь на канал, пишите комментарии. Это будет стимулом для меня писать больше статей.
Удачи!
А как бы вы решили данную задачу?
