Порада LuckyTemplates: як порівняти перші N робочих днів

Для цієї публікації я хотів би поговорити про нещодавню пораду LuckyTemplates, яку я придумав для члена . Запитання стосувалося корисної моделі для порівняння перших N робочих днів або перших N оплачуваних днів певного місяця порівняно з тим самим періодом попереднього місяця. Ви можете переглянути повне відео цього підручника внизу цього блогу.

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

Я думаю, що тут є справді цікаве рішення Power Query . Я також працював із цікавим рішенням DAX над цією проблемою. Я розгляну останній в іншому посібнику.

Я також хочу розповісти вам про те, що я використовував для LuckyTemplates Challenge #16. Я використовував індикатор прокручування , який дуже добре працюватиме для цієї конкретної проблеми.

Але в цій пораді LuckyTemplates я торкнуся рішення запиту потужності. Давайте перейдемо до LuckyTemplates і поглянемо на наші дані. Для цього я змоделював дані за допомогою інструменту набору практичних даних, який ми зібрали в LuckyTemplates, яким ви можете користуватися безкоштовно. Це чудово підходить для складання базового набору даних зіркової схеми та для тестування рішень.

Зміст

• Наша модель даних
• Створення дублюючої таблиці
• Об’єднання таблиці дат
• Створення спеціального стовпця
• Створення параметра What-If
• Створення показника кількості робочих днів
• Висновок

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

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

Я також трохи змінив цей набір даних. Я створив таблицю свят і пов’язав її з розширеною таблицею дат , щоб вона могла правильно розрахувати свята, які ми видалимо в цьому аналізі. Якщо ви не впевнені, як це зробити, перегляньте цей посібник .

Потім я відфільтрував перший неповний місяць у наборі даних, оскільки цей місяць мав лише кілька днів даних і не мав робочих днів, які нам потрібні. Зверніть увагу, що ми починаємо з першого повного місяця; але крім цього, це буде ваш стандартний набір даних. Тож приступаємоі почніть працювати над цією порадою LuckyTemplates.

Якщо ми подивимося на наші дані, ви побачите, що в певний день ми маємо різні замовлення. Мета полягає в тому, щоб пронумерувати все, що починається з 1 квітня, як наш перший робочий день, 2 квітня як наш другий робочий день, 3 квітня як наш третій робочий день і так далі. Ми пронумеруємо це від 1 до N робочих днів для кожного місяця без урахування вихідних і свят.

Створення дублюючої таблиці

Щоб розпочати роботу, нам потрібно створити дублікат цієї таблиці, щоб ми могли агрегувати загальний обсяг продажів і перенести його на рівень індивідуального спостереження. Ми назвемо цю повторювану таблицю Sales Aggregated .

Можливо, ви помітили, що ми дублюємо таблицю Sales , а не просто посилаємося на неї. У цьому випадку ми об’єднаємо цю таблицю з вихідною таблицею Sales. Якщо ми посилатимемося на нього, це спричинить проблеми, коли ми об’єднаємо ці два.

Перше, що потрібно зробити, це використати Group By у стовпці OrderDate . Натисніть кнопку «Додатково» , оскільки вона дасть нам повну картину того, що ми тут робимо. Ми додамо загальну кількість продажів за день як нову назву стовпця, і це буде сума загальної суми нашого рядка.

Тепер ми маємо кожну дату (перший стовпець) і загальний обсяг продажів за цей день (другий стовпець).

Об’єднання таблиці дат

Наступним кроком у цій пораді LuckyTemplates є об’єднання цього з нашою таблицею дат, щоб визначити, які з цих днів є робочими, а які – ні. Ми можемо знову згрупувати за , розділити їх і пронумерувати відповідним чином.

Ми об’єднаємо нашу таблицю Dates і зв’яжемо OrderDate з Date . У нижній частині ми бачимо, що вибір відповідає кожному рядку таблиці, як і повинно бути.

Поле, яке нас цікавить, це IsBusinessDay .

Це основне поле, за яким ми будемо групувати, а потім використовувати поля «Місяць і рік» і «МісяцьРік» .

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

Як тільки ми натиснемо кнопку OK , ми отримаємо саме те, що нам потрібно.

Ми також повинні переконатися, що наша дата замовлення відсортована в порядку зростання.

Потім ми повертаємось і створюємо другу Group By .

Цього разу ми натиснемо кнопку «Додатково» , згрупуємо «Місяць і рік» , а потім додамо друге групування.

Друге групування призначене для IsBusinessDay , тому ми можемо пронумерувати ті, де робочі дні є справжніми.

Потім ми створимо функцію AllRows , щоб вона не агрегувала, і ми зможемо повернутися до початкового рівня деталізації.

Це має дати нам вкладені таблиці , де ми можемо бачити таблицю для істини та таблицю для хибності для кожного місяця.

Створення спеціального стовпця

Далі нам потрібно створити спеціальний стовпець , щоб додати необхідну кількість робочих днів.

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

Потім ми можемо видалити перші три стовпці та розширити наш четвертий спеціальний стовпець.

Якщо ми подивимося на результат, він дає нам індекс дня, який підраховує робочі дні (true), а потім підраховує вихідні та святкові дні кожного місяця (false). Далі ми маємо перевірити, чи вони мають належний тип поля.

Повернемося до таблиці продажів. Ми об’єднаємо таблицю Aggregate, яку щойно створили, з таблицею Sales.

Ми об’єднаємо їх у OrderDate . Ви можете побачити внизу, що він відповідає всім рядкам першої таблиці.

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

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

Наша підготовча робота над запитом потужності завершена.

Створення параметра What-If

Пам’ятайте, що в оригінальному завданні ми хочемо змінити кількість днів, які ми дивимося, від 5 до 10 і від 15 до 20. Я думаю, що найкращий спосіб зробити це за допомогою нового параметра «що-якщо » .

Ми назвемо параметр «що-якщо » First N Business Days , а його типом даних буде ціле число. Мінімальне значення становить 1, а максимальне — 20. Тоді ми встановлюємо за замовчуванням значення 5, яке є першим значенням, яке бажає користувач.

Тепер у нас є зріз, який автоматично створює показник урожаю, щоб зафіксувати значення зрізу.

Давайте створимо таблицю з нашими результатами. Давайте розмістимо наш розмір місяця та року на полотні, а потім відсортуємо його за місяцем і роком .

Після того, як ми правильно відсортували це поле, ми можемо просто відкинути вимірювання загальних продажів і розгорнути його.

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

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

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

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

Наприклад, якщо повзунок стоїть на 5, нам потрібні всі дні таблиці Sales, у яких індекс дня менший або дорівнює 5.

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

Нарешті, поверніть контекст для місяця та року та закрийте вимірювання. Ми повинні бути готові до цього моменту.

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

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

Висновок

У цій пораді LuckyTemplates ми говорили про те, як динамічно порівнювати подібні за подібними за допомогою рішення Power Query. Ми можемо помістити це в стовпчасту або лінійну діаграму.

У деяких із наших майбутніх посібників ми розглянемо рішення DAX і запровадимо візуалізацію скролера KPI.

Якщо вам сподобався вміст, розглянутий у цьому посібнику, не забудьте підписатися на телеканал LuckyTemplates .