DAX
October 11

Биг дата. Часть 4.

В предыдущей части мы определились с тем, что при работе с датами без календаря никуда. В этой части хочу разобрать несколько популярных вопросов при работе с датами.

  • Как скрыть будущие месяцы/даты?
  • Как сравнить с предыдущей неделей?
  • Как сравнить 10 дней с предыдущими 10 днями?
  • Как показать предыдущие N дней, недель месяцев...?
  • Как сравнить произвольные периоды?

Воспользуемся простой моделью из предыдущей части:

Схема модели

Как скрыть будущие месяцы/даты?

Прежде чем ответить на этот вопрос, создадим меру накопительного итога:

Накопительная сумма заказов

Добавим созданную меру в визуализации:

Значения в "будущих" периодах

Как видите, мера показывает значения до конца года, даже в те месяцы, когда еще не было продаж. Можно пойти сложным путём - дописать меру, выполняя проверку на фактическое значение:

Значения в "будущих" периодах скрыты

Но более гибкий и простой способ - это создать в календаре специальный столбец "завершённый период", то есть пометить все даты, которые уже прошли:

Столбец в Календаре "Прошедший период"

После чего данный столбец можно использовать в качестве фильтра как для всего отчёта, так и для отдельных визуализаций или мер:

"Будущие" периоды скрыты с помощью фильтра

Как сравнить с предыдущей неделей?

В DAX много функций Time Intelligence, но все они работают с четырьмя стандартными измерениями: день, месяц, квартал, год. Для того чтобы работать с другими типами измерений, например - неделя, требуется написание собственных функций. Чтобы облегчить решение задачи сравнения с предыдущей неделей, добавим в календарь сквозную нумерацию недели:

Столбец в Календаре "Сквозная неделя"

В выражении сначала определяем дату первого дня недели - понедельника, а затем делим на семь дней, чтобы получить целое число номера недели.

Можно конечно для красоты пронумеровать недели от 1 до N, но не вижу в этом смысла, поэтому использую нумерацию "от начала времён", то есть от первой даты, которая используется в DAX - 31/12/1899.

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

Мера суммы заказов за предыдущую неделю

Добавим полученную меру в визуализацию:

Сравнение недель

Мера отлично работает, кроме "пересекающихся" недель при смене года, когда для последней и первой недели года показывается одинаковое значение. Для решения этого вопроса прежде всего необходимо определить бизнес логику, как смотреть/сравнивать недели. Возможно, решением будет смотреть не на "год-неделя", а также на сквозные недели. Или работать с неделями ISO и относить "остаток" недели или к последней неделе, или к первой неделе года. Если же хочется сравнивать сопоставимые по количеству дней значения, то, выше указанную меру, можно ещё немного доработать:

Мера суммы заказов за предыдущую неделю с учетом дней недели
Сравнение недель с учетом дней недели

Теперь последняя и первая неделя года вполне сопоставимы с предыдущей неделей, а дополнительно мы сможем сравнивать и неделю с неделей даже выбрав определенные дни недели.

Как сравнить 10 дней с предыдущими 10 днями?

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

Создаём меру:

Мера с простым сдвигом на N дней

Проверяем в визуализации:

Результат сравнения

Рядом специально для проверки создал несвязанные таблицу и срез для проверки. Всё работает отлично, кроме того, что набор дней недели разный.

Снова и снова повторяю: сначала бизнес вопрос, который решаем, затем решение.

Сформулируем его следующим образом: продажи за БЛИЖАЙШИЙ аналогичный период - с таким же набором дней недели.

Корректируем меру:

Мера аналогичного периода

Проверяем работу меры:

Проверка работы меры аналогичного периода

Теперь у нас полностью сопоставимые периоды, которые начинаются с одного дня недели.

Как показать предыдущие N дней, недель месяцев...?

Ещё один довольно часто встречающийся сценарий, когда пользователь не хочет проставлять множество галочек выбирая нужный диапазон, а хочет указать начальную или конечную точку (дату, месяц), а в визуализации чтобы отобразилось N предыдущих/следующих значений.

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

Например: на странице установлен фильтр по текущему месяцу, но при этом в одной из диаграмм необходимо показать помесячную динамику с начала года:

И верхняя, и нижняя диаграммы показывают не тот результат, который ожидается. В верхней диаграмме действует фильтр из среза, оставляя только один месяц. А для нижней диаграммы взаимодействие со срезом отключено, из-за чего отображаются значения за все периоды, а не за текущий год. Это можно исправить добавив ещё один срез с фильтром по году, или задать его же в панели фильтров, но при выборе пользователем месяца другого года придётся также изменять и второй фильтр. Цель же автоматически показывать динамику по месяцам того года, месяц которого пользователь выбрал в срезе.

Итак, создадим копию справочника "Календарь":

Заменим в диаграмме поле [Year-Month] из основного календаря на такое же поле из дубля:

У нас по прежнему на диаграмме отображается одно значение, так как фильтр по текущему месяцу из основного календаря всё ещё действует, а значения без данных скрываются по умолчанию. Проверим:

Действительно, по измерению нет ни прямых, ни кросс фильтров. Только на меру действует контекст вычисления. Чтобы решить задачу с динамикой, кроме дополнительного календаря, создадим специальную меру:

Мера для диаграммы динамики

Проверяем в визуализации:

Как видите, в зависимости от выбранного месяца меняется и динамика по году.

Как сравнить произвольные периоды?

Для решения данной задачи также необходимо наличие второго календаря.

Создадим два среза, в которых будут выбраны периоды для сравнения:

Взаимодействие двух календарей с основной мерой

Основная мера возвращает пустоту, так как оба календаря связаны с таблицей фактов активной связью и создают взаимоисключающий контекст вычисления - продажа не может быть одновременно и в октябре, и в апреле. Поэтому создадим специальные меры:

Меры расчёта суммы заказов по каждому из календарей

Результат:

Вывод значений по каждому из срезов

Для решения данной задачи есть ещё несколько способов, но данный считаю наиболее оптимальным, так как задействуются физические связи, которые работают наиболее быстро, а созданные меры просты для понимания.

Посмотреть отчёт можно ниже:

А также ссылка на скачивание файла.

PS Оставляйте комментарии с кейсами по работе с датами, которые кажутся вам интересными или у вас не получается решить. По возможности или дополню статью, или напишу новую ;-)