Биг дата. Часть 4.
В предыдущей части мы определились с тем, что при работе с датами без календаря никуда. В этой части хочу разобрать несколько популярных вопросов при работе с датами.
Воспользуемся простой моделью из предыдущей части:
Как скрыть будущие месяцы/даты?
Прежде чем ответить на этот вопрос, создадим меру накопительного итога:
Добавим созданную меру в визуализации:
Как видите, мера показывает значения до конца года, даже в те месяцы, когда еще не было продаж. Можно пойти сложным путём - дописать меру, выполняя проверку на фактическое значение:
Но более гибкий и простой способ - это создать в календаре специальный столбец "завершённый период", то есть пометить все даты, которые уже прошли:
После чего данный столбец можно использовать в качестве фильтра как для всего отчёта, так и для отдельных визуализаций или мер:
Как сравнить с предыдущей неделей?
В DAX много функций Time Intelligence, но все они работают с четырьмя стандартными измерениями: день, месяц, квартал, год. Для того чтобы работать с другими типами измерений, например - неделя, требуется написание собственных функций. Чтобы облегчить решение задачи сравнения с предыдущей неделей, добавим в календарь сквозную нумерацию недели:
В выражении сначала определяем дату первого дня недели - понедельника, а затем делим на семь дней, чтобы получить целое число номера недели.
Можно конечно для красоты пронумеровать недели от 1 до N, но не вижу в этом смысла, поэтому использую нумерацию "от начала времён", то есть от первой даты, которая используется в DAX - 31/12/1899.
Далее остаётся написать достаточно простую меру, которая будет считать продажи за предыдущую неделю:
Добавим полученную меру в визуализацию:
Мера отлично работает, кроме "пересекающихся" недель при смене года, когда для последней и первой недели года показывается одинаковое значение. Для решения этого вопроса прежде всего необходимо определить бизнес логику, как смотреть/сравнивать недели. Возможно, решением будет смотреть не на "год-неделя", а также на сквозные недели. Или работать с неделями ISO и относить "остаток" недели или к последней неделе, или к первой неделе года. Если же хочется сравнивать сопоставимые по количеству дней значения, то, выше указанную меру, можно ещё немного доработать:
Теперь последняя и первая неделя года вполне сопоставимы с предыдущей неделей, а дополнительно мы сможем сравнивать и неделю с неделей даже выбрав определенные дни недели.
Как сравнить 10 дней с предыдущими 10 днями?
Схожая с предыдущей задача, но период более нестандартный - количество дней может быть любым, в том числе и неделя (семь дней), но начинающаяся с любого дня недели. Главное - это то, что пользователь выбирает произвольный период, а мера должна рассчитать значение за аналогичный по количеству дней предыдущий.
Рядом специально для проверки создал несвязанные таблицу и срез для проверки. Всё работает отлично, кроме того, что набор дней недели разный.
Снова и снова повторяю: сначала бизнес вопрос, который решаем, затем решение.
Сформулируем его следующим образом: продажи за БЛИЖАЙШИЙ аналогичный период - с таким же набором дней недели.
Теперь у нас полностью сопоставимые периоды, которые начинаются с одного дня недели.
Как показать предыдущие N дней, недель месяцев...?
Ещё один довольно часто встречающийся сценарий, когда пользователь не хочет проставлять множество галочек выбирая нужный диапазон, а хочет указать начальную или конечную точку (дату, месяц), а в визуализации чтобы отобразилось N предыдущих/следующих значений.
Для решения этой задачи не обойтись без создания второго календаря или вспомогательных таблиц с нужными измерениями, так как, если для мер можно изменить контекст вычисления, то для измерения (оси) фильтр или есть, или его нет.
Например: на странице установлен фильтр по текущему месяцу, но при этом в одной из диаграмм необходимо показать помесячную динамику с начала года:
И верхняя, и нижняя диаграммы показывают не тот результат, который ожидается. В верхней диаграмме действует фильтр из среза, оставляя только один месяц. А для нижней диаграммы взаимодействие со срезом отключено, из-за чего отображаются значения за все периоды, а не за текущий год. Это можно исправить добавив ещё один срез с фильтром по году, или задать его же в панели фильтров, но при выборе пользователем месяца другого года придётся также изменять и второй фильтр. Цель же автоматически показывать динамику по месяцам того года, месяц которого пользователь выбрал в срезе.
Итак, создадим копию справочника "Календарь":
Заменим в диаграмме поле [Year-Month] из основного календаря на такое же поле из дубля:
У нас по прежнему на диаграмме отображается одно значение, так как фильтр по текущему месяцу из основного календаря всё ещё действует, а значения без данных скрываются по умолчанию. Проверим:
Действительно, по измерению нет ни прямых, ни кросс фильтров. Только на меру действует контекст вычисления. Чтобы решить задачу с динамикой, кроме дополнительного календаря, создадим специальную меру:
Как видите, в зависимости от выбранного месяца меняется и динамика по году.
Как сравнить произвольные периоды?
Для решения данной задачи также необходимо наличие второго календаря.
Создадим два среза, в которых будут выбраны периоды для сравнения:
Основная мера возвращает пустоту, так как оба календаря связаны с таблицей фактов активной связью и создают взаимоисключающий контекст вычисления - продажа не может быть одновременно и в октябре, и в апреле. Поэтому создадим специальные меры:
Для решения данной задачи есть ещё несколько способов, но данный считаю наиболее оптимальным, так как задействуются физические связи, которые работают наиболее быстро, а созданные меры просты для понимания.
А также ссылка на скачивание файла.
PS Оставляйте комментарии с кейсами по работе с датами, которые кажутся вам интересными или у вас не получается решить. По возможности или дополню статью, или напишу новую ;-)