Биг дата. Часть 1.
Самые частые вопросы, которые задают при работе с языком DAX, касаются темы работы с датами. Это настолько обширная тема, что я решил разделить её на несколько частей.
И, в первой части, я хочу рассказать о типичных ошибках при работе с датами у начинающих аналитиков.
Как настоящий начинающий аналитик я загрузил в свою модель одну таблицу из Excel, которая содержит как мне кажется все нужные мне поля. В том числе и два столбца с датами: Orders[Order Date] - дата заказа и Orders[Ship Date] - дата отгрузки.
Для начала создадим простую меру, которая будет подсчитывать количество единиц товара:
Далее создадим простую визуализацию в виде таблицы, чтобы посмотреть сколько у нас заказывали по месяцам. И уже здесь мы сталкиваемся с первой проблемой: в таблице есть два столбца с датами, но как их агрегировать до года и месяца?
Чуть более опытные аналитики скажут, что есть же иерархия дат - и будут правы, но про неё мы поговорим во второй части.
Для этого создадим в таблице соответствующие столбцы: [Year Number], [Month Number], [Month Name] и [Year-Month]
Такие столбцы могут уже присутствовать в выгрузке или их можно создать в PowerQuery, но пока отмечу только то, что, чем больше столбцов, чем шире таблица, тем хуже происходит сжатие данных, а значит медленнее работает ваша модель.
Вернёмся к созданию визуализации. Добавим в таблицу столбец Orders[Year-Month] и ранее созданную меру [SUM Order Quantity]
Создадим ещё одну меру, которая будет показывать нам объёмы заказов за предыдущий месяц, чтобы можно было сравнить с текущим. Для этого воспользуемся функцией PREVIOUSMONTH
Ещё одна классическая ошибка, которую допускают новички, это использование в функциях Time Intelligence вместо столбца с датой столбца с "нужной" гранулярностью. Например: в функцию PREVIOUSMONTH подставляют столбец с номером месяца, а в функцию PREVIOUSYEAR столбец с номером года, которые имеют тип "Целое число", из-за чего функции не работают.
Добавляем меру в таблицу и видим, что она не работает, так как везде показывает пустоту:
Почему мера не работает? Всё очень просто - функция PREVIOUSMONTH меняет контекст вычисления ТОЛЬКО по столбцу Orders[Order Date], не затрагивая другие столбцы, а в текущем контексте, например, второй строки визуализации, у нас действует фильтр 2012-02. Соответственно, у нас не может быть заказ одновременно в январе и феврале 2012 года, поэтому получаем пусто.
Чтобы получить значение за предыдущий месяц, необходимо удалить фильтр по столбцу Orders[Year-Month]. Для этого модифицируем меру:
Как видно на скриншоте, значения за предыдущий месяц подтягиваются корректно.
Усложним немного задачу. Изменим тип визуализации с таблицы на матрицу и вместо столбца Orders[Year-Month] будем использовать столбцы Orders[Year Number] и Orders[Month Name]
И вновь мы видим пустые значения в мере по предыдущему месяцу. Если вы внимательный читатель, то уже догадались, почему так произошло - у нас появился новый контекст вычисления столбцы Orders[Year Number] и Orders[Month Name], по которым мы не сбрасываем фильтр.
Но, как мы видим, мера по прежнему работает не так, как мы ожидали. Появились значения в строке подитога по году, которые равны декабрю, но в строках месяцев по прежнему пусто. Здесь мы не учли ещё один нюанс "скрытый" контекст - месяцы у нас идут не в алфавитном, а календарном порядке. Для этого используется столбец сортировки Orders[Month Number], который также добавляется в контекст вычисления.
Вернёмся к мере и допишем этот столбец, чтобы сбросить фильтр и по нему:
Итак, мы получили нужный результат, но чтобы его добиться нам пришлось усложнить написание меры, чтобы учесть различные сценарии и нюансы. Но, как говорится: это ещё только цветочки.
Вы помните, что в нашей таблице есть ещё один столбец с датами Orders[Ship Date] - дата отгрузки. Отгрузка производится как правило через несколько дней, после заказа, а значит и количество за период (без учёта других факторов), будет различаться.
И вновь, чтобы агрегировать количество по месяцам, нам необходимо создать дополнительные столбцы. На этот раз на основе столбца Orders[Ship Date]:
Создадим ещё одну визуализацию, только теперь в строках будет столбец Orders[Ship Year-Month], который мы создали по дате отгрузки
В зависимости от столбца агрегации мы видим разные значения одной и той же меры для одного периода. Но как показать значения заказов и значения отгрузок в одной визуализации за один период корректно?
Для этого нам придётся создать специальную меру, которая будет считать сумму отгрузок в текущем контексте:
Добавим созданную меру в визуализацию, в которой используется столбец Orders[Year-Month], созданный на основе Orders[Order Date]:
По месяцам всё считается корректно, но вот в строке итогов значение стало меньше. Почему?
Как мы видим, заказы заканчиваются в декабре 2015, а последние отгрузки были в январе 2016. А так как мы в основной визуализации использовали столбцы агрегирования основанные на дате заказа, то мы потеряли часть данных, что приводит к некорректным подсчётам. Можно было бы подумать, что "тогда надо переписать меры и использовать столбцы на основе даты отгрузки", но тогда мы можем столкнуться с обратной ситуацией, когда у нас "пропадут" данные по заказам, которые были сделаны в периоды, в которые не было отгрузок.
И, разберём ещё один нюанс, который на мой взгляд является главным недостатком модели для анализа построенной на одной таблице.
Добавим на лист визуализации срез по странам(Orders[Country]) и выберем Belarus.
Первое, что мы видим, это уже разобранная ошибка, когда отгрузки осуществлялись в месяц, в котором не было заказов, например, 2012-09 - из-за чего мы "потеряли" часть отгрузок.
Второе, в визуализации отсутствуют месяца, когда не было заказов и мы ни как не сможем их отобразить, так как столбец Orders[Year-Month] находится в одной таблице с Orders[Country], и соответственно фильтруется этим столбцом, ограничивая набор только связанными значениями, а фильтр можно менять только внутри меры.
Как видите, казалось бы такая простая модель данных, состоящая всего из одной таблицы, доставляет слишком много трудностей:
UPD: Ещё раз отмечу, что использование функций Time Intelligence применительно к таблице фактов - это ошибка. Даже, если вы получили верный результат сейчас, то в следующий раз вы можете столкнуться с ошибкой. И хорошо, если вы заметите это сразу.
О том, как этого избежать, поговорим в следующих частях.
ps Ссылка на архив с отчётом и исходным файлом для модели