Получите значения LuckyTemplates за предыдущую неделю с помощью DAX и Power Query

Получение значений за предыдущий период в LuckyTemplates — обычная задача, но получение значений за предыдущую неделю требует особого внимания. В этом руководстве я рассмотрю два простых и универсальных метода получения значений LuckyTemplates за предыдущую неделю, которые могут быть полезным дополнением к вашему набору инструментов: один с использованием DAX, а другой с помощью Power Query. Вы можете посмотреть полное видео этого урока в нижней части этого блога.

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

Оглавление

• Значения предыдущей недели в LuckyTemplates: проблема
• Получение LuckyTemplates за предыдущую неделю с помощью DAX
• Заключение

Значения предыдущей недели в LuckyTemplates: проблема

Если мы посмотрим на номер недели, у нас будет 52 недели; умножьте это на семь дней (неделю) и получите 364. Итак, в каждом году есть дополнительный день, а затем в високосных годах есть два дополнительных дня. Эти один или два дополнительных дня в конечном итоге вызывают много проблем.

Давайте создадим меру из нашей таблицы Dates. Мы назовем его MAX Week # (номер). В этом анализе мы используем таблицу расширенных дат LuckyTemplates; у этого есть ISO Weeknumber , что весьма полезно. Теперь давайте возьмем MAX номера недели ISO.

Теперь давайте возьмем таблицу и отбросим год , а также нашу максимальную неделю # . Этот набор данных содержит данные за 10 лет, и мы видим, что первые два года имеют 52 недели. Затем у нас есть два года с 53 неделями, три года с 52 неделями, а затем еще два года с 53 и один год с 52.

Здесь нет четкой закономерности. Нелегко вернуться назад и предсказать, что в одном году у вас будет 52 недели, а в другом — 53.

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

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

Глядя на текущий период (например, 20 января, который является периодом создания этого руководства), мы видим, что смещение равно нулю . Принцип работы смещений заключается в том, что для текущего периода смещение становится равным нулю.

Неделя, предшествующая текущей неделе, — это отрицательная единица, две недели до текущей недели — это отрицательная двойка и так далее. И затем он идет вперед от положительных чисел; на следующей неделе получает единицу, через две недели получает двойку и т. д.

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

Получение LuckyTemplates за предыдущую неделю с помощью DAX

Давайте создадим новую меру и назовем ее Предыдущая неделя # . Мы собираемся начать с переменной ( SelWkOffset ), где мы просто собираемся сделать SELECTEDVALUE для WeekOffset . Итак, в текущей строке он нарисует этот WeekOffset и поместит его в нашу переменную.

Следующей переменной будет Result . Это конечный результат, которого мы хотим, и мы собираемся использовать CALCULATE , потому что мы собираемся изменить контекст.

Затем нам нужен номер недели MAX ISO , и это будет зависеть от этих условий фильтра. Итак, мы удалим все фильтры в таблице «Даты», поэтому здесь будем использовать ВСЕ . Если бы у нас был срез даты, мы могли бы использовать ALLSELECTED , но сейчас мы этого не делаем, поэтому мы просто будем использовать ALL, чтобы удалить фильтр из этой таблицы Dates.

И затем мы скажем, что WeekOffset будет равен нашему SelWkOffset минус один . Именно здесь СМЕЩЕНИЯ становятся такими мощными. Вы не можете использовать номер недели минус один, потому что он сбрасывается каждый год. Но поскольку ВСЕ СМЕЩЕНИЯ являются последовательными, вы можете относиться к ним так же, как к поиску предыдущего года и простому вычитанию единицы за каждый предыдущий год. Итак, это смещение работает точно так же, как последовательное число.

Теперь мы можем взять и закрыть это условие фильтра, закрыть ВЫЧИСЛИТЬ и просто ВЕРНУТЬ наш результат .

Теперь давайте посмотрим, как работает эта мера. В приведенной ниже таблице мы видим, что он делает именно то, на что мы рассчитывали. На 52-й неделе для первого периода следующего года предыдущая неделя будет 52-й. Затем он отступает на один шаг назад в каждом периоде и так далее.

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

Допустим, вы работаете с корпоративной таблицей дат, которая поступает из вашего хранилища данных и не включает эти смещения. Есть техника, которую я хочу показать вам в Power Query. Впервые я увидел это в записи блога Имке Фельдманн.

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

Итак, давайте сошлемся на эту таблицу Dates Raw и назовем ее Dates . Затем мы обязательно отсортируем это в порядке возрастания .

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

Далее мы хотим добавить эти индексные столбцы. Здесь важно то, что первый индекс, который мы добавим, будет индексом с отсчетом от 0. Затем мы добавим второй индекс, основанный на 1.

Затем мы возьмем этот столбец и объединим таблицу саму с собой. Мы собираемся объединить его на основе двух разных столбцов индекса. Это даст нам сдвиг в рядах.

Итак, если мы возьмем это и объединим первый с индексом, основанным на 0, с индексом, основанным на 1, он будет соответствовать 521 в 522 строках. Это именно то, чего мы ожидали, потому что вторая таблица не получит нулевой индекс, поскольку она начинается с единицы.

После этого мы просто расширим это должным образом, просто используя номер недели ISO.

Это дает нам номер предыдущей недели. А теперь мы просто расширим исходную группу и удалим поле WeekEnding и исходный номер недели ISO, чтобы не дублировать.

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

Заключение

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

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

Ваше здоровье!