Совет LuckyTemplates: как сравнить первые N рабочих дней

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

TJ Henneman хотел сравнить первые 5 оплачиваемых дней текущего месяца с первыми 5 оплачиваемыми днями предыдущего месяца, а затем 10-й, 15-й и 20-й день. Обратите внимание, что он хочет посмотреть только оплачиваемые дни, т.е. так что это не включает выходные и праздничные дни. Вы можете проверить его сообщение .

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

Я также хочу рассказать вам о недавней методике создания отчетов, которую я использовал для LuckyTemplates Challenge #16. Я использовал индикатор KPI с прокруткой , который отлично подойдет для решения этой конкретной проблемы.

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

Оглавление

• Наша модель данных
• Создание дублирующей таблицы
• Объединение таблицы дат
• Создание пользовательского столбца
• Создание параметра «что, если»
• Создание показателя количества рабочих дней
• Заключение

Наша модель данных

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

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

Затем я отфильтровал первый неполный месяц в наборе данных, потому что в этом месяце было всего несколько дней данных и не было рабочих дней, которые нам нужны. Обратите внимание, что мы начинаем с первого полного месяца; но кроме этого, это будет ваш стандартный набор данных. Итак, давайте перейдем к запросу мощности и начнем работать над этим советом по LuckyTemplates.

Если мы посмотрим на наши данные, вы увидите, что у нас разные заказы в определенный день. Цель состоит в том, чтобы нумеровать все, что начинается с 1 апреля, как наш первый рабочий день, 2 апреля, как наш второй рабочий день, 3 апреля, как наш третий рабочий день, и так далее. Мы будем нумеровать это от 1 до N рабочих дней для каждого месяца, исключая выходные и праздничные дни.

Создание дублирующей таблицы

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

Вы могли заметить, что мы продублировали таблицу Sales , а не просто сослались на нее. В этом случае мы собираемся соединить эту таблицу с исходной таблицей Sales. Если мы сошлемся на него, это вызовет проблемы при их соединении.

Первое, что нужно сделать, это использовать Group By для столбца OrderDate . Нажмите кнопку «Дополнительно» , потому что это даст нам полную картину того, что мы здесь делаем. Мы поместим Total Day Sales в качестве имени нашего нового столбца, и это будет сумма нашей общей суммы по строке.

Теперь у нас есть каждая дата (первый столбец) и общий объем продаж за этот день (второй столбец).

Объединение таблицы дат

Следующий шаг в этом совете LuckyTemplates — объединить его с нашей таблицей «Даты», чтобы выяснить, какие из этих дней являются рабочими, а какие — нет. Мы можем снова сгруппировать по , разделить их и соответствующим образом пронумеровать.

Мы объединим нашу таблицу Dates и свяжем OrderDate с Date . В нижней части мы видим, что выбор соответствует каждой строке таблицы, как и должно быть.

В первую очередь нас интересует поле IsBusinessDay .

Это основное поле, по которому мы будем группировать, а затем использовать поля Month & Year и MonthnYear .

Нам также нужно проверить DayofWeekName , чтобы убедиться, что мы отфильтровываем правильные дни.

Как только мы нажмем кнопку OK , это даст нам именно то, что нам нужно.

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

Затем мы возвращаемся и создаем вторую группу Group By .

На этот раз мы нажмем кнопку «Дополнительно» , сгруппируем по месяцу и году , а затем добавим вторую группу.

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

Затем мы создадим функцию AllRows , чтобы она не агрегировалась, и мы смогли вернуться к исходному уровню детализации.

Это должно дать нам вложенные таблицы , где мы можем увидеть таблицу для истинного и ложного для каждого месяца.

Создание пользовательского столбца

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

Мы собираемся использовать функцию Table.AddIndexColumn , которая будет работать с таблицей AllRows , и назовем ее Day Index . Мы хотим, чтобы индекс начинался с 1 и увеличивался на 1 каждый новый рабочий день.

Затем мы можем удалить первые три столбца и расширить наш четвертый пользовательский столбец.

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

Вернемся к нашей таблице продаж. Мы объединим таблицу Aggregate, которую мы только что создали, с нашей таблицей Sales.

Мы объединим их в OrderDate . Вы можете видеть внизу, что он соответствует всем строкам из первой таблицы.

Когда мы раскрываем столбец Sales Agg, выбираем новые поля, которые мы только что добавили: столбцы Month & Year , DayOfWeekName , MonthnYear , IsBusinessDay и DayIndex .

Давайте также очистим наши данные, прежде чем мы нажмем «Закрыть и применить» . Давайте переместим таблицу Sales Agg в наш раздел Data Prep , а затем выгрузим эту таблицу, так как она нам не понадобится в модели данных. Затем нажмите «Закрыть и применить» .

Наша работа по подготовке запроса мощности завершена.

Создание параметра «что, если»

Помните, что в исходной задаче мы хотим варьировать количество рассматриваемых дней с 5 до 10 и с 15 до 20. Я думаю, что лучший способ сделать это — использовать новый параметр «что, если » .

Мы назовем параметр «что, если» «Первые N рабочих дней» , а его тип данных будет целым числом. Минимальное значение устанавливается равным 1, а максимальное — 20. Затем мы установим по умолчанию значение 5, которое является первым значением, которое хотел пользователь.

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

Создадим таблицу с нашими результатами. Давайте поместим наше измерение Month & Year на холст, а затем отсортируем его по Month & Year .

Как только мы правильно отсортируем это поле, мы можем просто удалить показатель Total Sales и расширить его.

Создание показателя количества рабочих дней

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

Мы назовем эту новую меру Total Sales N Bus Days . Мы начнем с функции , поскольку мы определенно меняем контекст, и с нашей меры общего объема продаж. Мы собираемся отфильтровать это с помощью нашей таблицы Sales.

Следующим шагом нужно написать условия, которые мы хотим учитывать для ползунка. У нас есть индекс дня продаж, который мы создали в Power Query, и мы хотим, чтобы он был меньше или равен значению первых N рабочих дней, которое является значением, собранным срезом.

Например, если ползунок стоит на 5, нам нужны все дни в таблице «Продажи», где индекс дня меньше или равен 5.

А потом мы хотим убрать нерабочие дни. Мы будем ссылаться на таблицу Date и использовать поле IsBusinessDay. Мы будем рассматривать только тот результат, который ИСТИНЕН.

Наконец, верните контекст для месяца и года и закройте меру. Мы должны быть готовы идти к этому моменту.

Давайте поместим новую меру в нашу таблицу.

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

Заключение

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

В некоторых из наших будущих руководств мы рассмотрим решение DAX и реализуем визуализацию скроллера KPI.

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