Time Intelligence в DAX: как динамически выбирать начальный период

В этом сообщении блога мы рассмотрим некоторые неприятные проблемы в области анализа времени, в частности проблемы, вызванные детализацией по неделям, и проблемы, которые могут быть вызваны нерегулярным количеством недель в году. Мы будем использовать смещения, чтобы получить точную информацию о времени в DAX.

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

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

Оглавление

• Примеры сценариев Time Intelligence в DAX
• Использование смещений для анализа времени в DAX
• Реализация стратегии резиновой уточки для анализа времени в DAX
• Сбор смещений с даты начала
• Расчет месячного диапазона
• Вычисление смещений начального и конечного месяцев
• Расчет недельного диапазона
• Еженедельный расчет в пределах диапазона
• Заключение

Примеры сценариев Time Intelligence в DAX

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

Еще одна причина использовать это, если вы хотите визуализировать это анимированным способом. Это в основном возьмет ваши данные и покажет их динамически, как только вы нажмете на доступ к игре.

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

Давайте перейдем к LuckyTemplates и сначала посмотрим на нашу модель данных. Это действительно простая модель данных с расширенной таблицей дат и таблицей спотовых цен , связанной с датами.

Использование смещений для анализа времени в DAX

Одна из вещей, над которой мы будем работать, это смещения . Смещения в расширенной таблице дат рассчитываются динамически. Каждый раз, когда отчет либо обновляется, либо открывается, он проходит через M-код. Например, в месячном смещении текущему месяцу присваивается значение 0, предыдущему месяцу — -1, а двум месяцам назад — -2. Заглядывая в будущее, он присваивает следующему месяцу +1, а двум месяцам в будущем +2.

Это простая концепция, но невероятно эффективная, когда вы имеете дело с анализом времени в DAX и работаете с месяцами , кварталами и неделями . Если вы не используете смещение, это иногда создает изрядную сложность в ваших вычислениях.

Использование смещений означает использование непрерывного ряда чисел, в котором возврат на месяц назад всегда равен -1, а переход на месяц вперед всегда равен +1, независимо от того, в какой части года вы находитесь.

Реализация стратегии резиновой уточки для анализа времени в DAX

В прошлом я говорил о «резиновом уклонении» , то есть озвучивании вашей стратегии вслух до того, как вы начнете писать свой DAX.

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

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

Сбор смещений с даты начала

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

А затем мы хотим сдвинуть это смещение вперед на 12 месяцев, а затем смотреть только на даты, которые находятся в пределах этого набора смещений. Позвольте мне показать вам, как это выглядит в DAX.

Расчет месячного диапазона

Это наша мера для Within Range Monthly , где мы выбираем наш год (который мы собрали из несвязанной таблицы годов) и наш месяц (который мы собрали из несвязанной таблицы месяцев).

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

Вычисление смещений начального и конечного месяцев

Давайте посмотрим на смещение начального месяца , рассчитав максимальное смещение. Мы удаляем все фильтры в таблице дат и фильтруем до выбранного месяца и выбранного года. Для каждого месяца должно быть только одно смещение, соответствующее этому месяцу и году.

С этого момента мы можем взять смещение конечного месяца , которое равно смещению начального месяца + 11 месяцев.

А затем мы смотрим на каждую выбранную дату и определяем, попадает ли она в смещение начального месяца и смещение конечного месяца. если он попадает в этот период, мы даем ему 1, а если нет, мы даем ему 0.

Если мы вернемся к просмотру месяца в нашем визуальном элементе, мы увидим визуальный элемент «В пределах диапазона» , который мы установили равным единице. Таким образом, он показывает только те месяцы в пределах смещения от начала до конца. Например, если мы нажмем на февраль, мы увидим февраль-январь.

Расчет недельного диапазона

Давайте посмотрим, как это выглядит с недельной точки зрения. Визуальное сначала начинается нормально и продолжается с 1 по 52 неделю. Пока все хорошо.

Но если мы нажмем на другие годы в срезе года, мы увидим 52-ю неделю, в то время как у некоторых есть 53-я неделя, что создает много проблем. Из этого примера видно, что проблема начинает проявляться.

У нас есть начальный период 2020 года и 15-я неделя , но конечный период — 2021 год и 13-я неделя вместо 2021 года и 14-й недели .

Если мы вернемся к 1-й неделе, то увидим, что она начинается хорошо, но заканчивается на 52-й неделе. Если мы посмотрим здесь на расчет 53-й недели, максимальное количество недель для 2020 и 2021 годов составляет 53 недели. Это не сработает для еженедельной детализации.

Давайте посмотрим, что мы можем сделать, чтобы заставить эту работу работать. Нам нужно исправить расчет DAX прямо здесь, в этой части:

Это связано с тем, что в некоторых случаях 51 является правильным, когда в году всего 52 недели, но для года, в котором 53 недели, этот последний период будет исключен. Это именно то, что мы видели в динамическом изображении за несколько недель, где при расчете был удален последний период в 2020 и 2021 годах.

Чтобы исправить это, мы переходим к показателю « В пределах диапазона еженедельно неправильно» , который на самом деле выглядит проще, чем предыдущая мера. У нас была начальная мера смещения, и мы рассчитали максимальное смещение. Затем мы убрали фильтр дат, а затем наложили фильтр на выбранную неделю и выбранный год с расчетом, что это приведет к правильному смещению.

Но это не работает, потому что не имеет значения, используете ли вы максимальное или минимальное смещение. Все, что мы делаем, — это обертываем агрегатор, чтобы не помещать голый столбец в инструкцию CALCULATE.

Но если мы вернемся к ISO WeekNumber , мы увидим, что год и номер недели не определяют однозначно недельное смещение в этом первом периоде.

Я придумал пуленепробиваемый способ сделать это. Вы можете использовать MIN, но имеет смысл создать эту меру смещения Week1 . Чтобы получить смещение недели 1, мы имеем дело со неделей 2, потому что она никогда не делится. Независимо от того, 52 или 53 недели в году, вторая неделя остается неизменной.

В этом расчете мы фильтруем до недели 2, чтобы получить смещение. И затем, как только мы получим это смещение за неделю 2, мы просто вычтем из него единицу. Это однозначно даст нам смещение первой недели. Это заканчивается решением нашей проблемы.

Еженедельный расчет в пределах диапазона

А затем мы возвращаемся к нашему еженедельному показателю «В пределах диапазона» и пишем оператор IF, в котором, если количество урожая относится к неделе 1, мы вычисляем смещение первой недели. Если это не неделя 1, мы просто вычисляем смещение начальной недели, как мы это делали в предыдущем ежемесячном расчете.

И тогда смещение конечной недели будет смещением начала недели + максимальный номер недели, который может быть либо 52, либо 53. Затем мы просто вычтем 1, чтобы не удваивать начальное смещение.

Мы можем сделать ту же конструкцию, которую мы использовали для месячного диапазона, чтобы отфильтровать недели, где все, что находится между началом и конечным смещением, получает 1, а все, что не получает 0.

Затем мы поместим еженедельную меру в пределах диапазона на панель фильтра. Все проверяется и выглядит точно.

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

Заключение

Это довольно глубокое погружение в анализ времени в DAX, где мы обсудили, как мы можем решить некоторые проблемы, связанные с номерами недель. Я надеюсь, что вы нашли этот учебник полезным и предоставили вам некоторые дополнительные инструменты в вашем наборе инструментов, когда вы имеете дело с проблемной недельной ситуацией.

Если вам понравился контент, описанный в этом руководстве, не забудьте подписаться на телеканал LuckyTemplates. У нас есть огромное количество контента, который постоянно выходит от меня и ряда создателей контента, и все они посвящены улучшению того, как вы используете LuckyTemplates и Power Platform.