Порівняння з попередніми даними так чи інакше часто присутнє у всіх звітах. Ця інформація допомагає зрозуміти тенденції – куди рухається бізнес, чи ми ростемо, і наші активності допомагають розвиватися компанії, або ми переживаємо період застою, чи навпаки, ситуація погіршується.
“Краще” / “гірше” – це слова порівняння. І бізнесу важливо не тільки розуміти як працює реклама, запущена місяць тому, а й бачити ширшу картину. У цьому допомагають розрахунки на рівні років – порівняння “рік-до-року” по місяцях та з накопичувальним підсумком. Сьогодні про них і поговоримо.
Але перед цим нагадаю: в одній з попередніх статей я вже писала про один зі способів порівняння даних в Power BI – автоматичне порівняння з попереднім періодом. Якщо це теж для вас актуально, лінк на ту статтю лишаю тут.
А сьогодні план наступний:
Знайомимось з моделлю даних
Я, як завжди, не хочу розпиляти вашу увагу і беру для моделі тільки те, що необхідно для розрахунків. Звісно, я працюю з більш розмаїтими таблицями, але у статті ті дані, які не використовуються в мірах, будуть тільки відволікати.
Як бачите, нам фактично потрібні тільки дати надходження доходу та сам дохід.
Оскільки для дат потрібна окрема таблиця-календар, я її створила і в'язала з основною таблицею доходу.
Календар можна створити багатьма способами. Найшвидший – створити його на DAX за допомогою однойменної функції CALENDAR, в яку потрібно додати 2 аргументи – початкову та кінцеву дату. Їх можна як прописати вручну, так і задати динамічно на основі, наприклад, таблиці фактів. Обидва ці способи описані в документації до цієї функції.
Пишемо розрахунки порівняння “рік-до-року”
Звісно, щоб порівнювати дохід, нам потрібна початкова міра, яка буде рахувати суму, і яку ми будемо використовувати в подальших мірах, моделюючи контекст фільтра для розрахунків доходу за період для порівняння. Якщо розуміння контексту фільтра вам дається складно, я вас розумію, як ніхто. Дуже раджу переглянути це відео з наочною демонстрацією того, як цей контекст працює в Power BI.
Міра сумарного доходу
А сама міра розрахунку доходу дуже проста – це сума значень в колонці revenue:
Total Revenue = SUM(fct_Revenue[revenue])
Міра доходу за попередній рік
Цікаве починається зараз, з моделюванням контексту фільтра.
Порівняння “рік-до-року” порівнює значення обраного року на рівні кварталів / місяців / днів, якщо потрібно, з тим же періодом минулого року.
Таким чином, ми порівнюємо, наприклад, дохід січня 2024-го з січнем 23-го, або перший квартал 24-го з першим кварталом 23-го і т.д.
Який би не був рівень розбивки періоду, вам потрібно порахувати дохід зі зміщенням на один рік назад.
Power BI має для цього дуже очевидну функцію, бо цей розрахунок надзвичайно популярний. Тому вам достатньо використати в функції CALCULATE разом з мірою [Total Revenue], яку ми створили на попередньому кроці, модифікатор фільтра SAMEPERIODLASTYEAR, який приймає всього один аргумент – колонку дат з таблиці-календаря.
Total Revenue PY =
CALCULATE(
[Total Revenue],
SAMEPERIODLASTYEAR(dim_Date[Date])
)На виході ми бачимо в таблиці, що сума Total Revenue PY ,навпроти January 2024, дорівнює сумі Total Revenue, напроти January 2023. І так далі, по місяцях.
Можливо, ви подумали, що в Power BI велика кількість таких спеціальних команд, і потрібно витратити дуже багато часу, щоб всі їх вивчити і тоді вже нормально працювати з PBI.
Якщо ви так подумали, то ви сильно помилились – розібратися й дійсно запам'ятати потрібно тільки основні принципи, контекст фільтра, контекст рядка і з десяток простих функцій, щоб більш-менш впевнено працювати з цим інструментом.
SAMEPERIODLASTYEAR насправді можна замінити простішою функцією – DATEADD. Проста вона тому, що більш загальновживана й універсальна, хоча аргументів в ній трохи більше. Якщо ви працюєте з BigQuery, то скоріш за все знаєте схожі функції – DATE_ADD і DATE_SUB.
Ось міра з DATEADD, яка теж приймає список дат з календаря та очікує від нас конкретний період зміщення і інтервал – мінус один рік:
Total Revenue PY2 =
CALCULATE(
[Total Revenue],
DATEADD ( dim_Date[Date], -1, YEAR )
)Результат в цьому випадку, звісно, такий же як і з SAMEPERIODLASTYEAR, але на відміну від цього модифікатора, з DATEADD ви можете задавати інші періоди в майбутнє і минуле, а не тільки один рік в минуле. Ідея моя була в наступному: знаючи більш прості й універсальні функції в PBI, ви зможете писати доволі складні цікаві розрахунки, навіть без знання чогось специфічного.
Порівнюємо дані
Ця міра надзвичайно проста:
Total Revenue YOY % =
DIVIDE(
[Total Revenue] - [Total Revenue PY],
[Total Revenue PY]
)Фактично ми ділимо різницю між доходом цього року й минулого на значення доходу минулого року. Матриця з розбивкою по місяцях уже робить решту справи – розраховує показник в розрізі місяця або року в кожному рядку таблиці:
Ось таким, доволі простим способом з мінімумом даних ми показали бізнесу, що справи у нього в першій половині 2023-го йшли добре, потім був період застою, а зараз справи йдуть не дуже, і потрібно приймати певні стратегічні рішення.
Пишемо порівняння з накопичувальним підсумком за рік
Наступний розрахунок, як на мене, є логічним продовженням попереднього, і розширює картину розвитку компанії з року в рік.
Міра з накопичувальним підсумком
Цей розрахунок теж дуже популярний, тому в PBI є окрема функція, яка рахує суму від початку до кінця року.
Це функція – TOTALYTD. У неї 4 аргументи, але зазвичай використовується 2, які є обов'язковими, а саме: перший – основна міра суми доходу, другий – список дат з календаря. Третій аргумент – фільтр, який можна додати, а четвертий – кінець фіскального року. Детальніше про цю функцію тут.
Total Revenue YTD =
TOTALYTD(
[Total Revenue],
dim_Date[Date]
)Як бачите, функція дуже лаконічна, але не дуже очевидно, що вона робить. Знову ж таки, якщо ви не знаєте чогось специфічного, можна написати це з допомогою інших, універсальніших функцій і більш наглядно.
Що таке по своїй суті розрахунок з накопичувальним підсумком за рік?
Це сума показника від початку року до поточного рядка. В нашому випадку нам цікаво порахувати наступне:
- сума в січні – це дохід за січень;
- сума в лютому – дохід на січень + лютий;
- сума в березні – дохід на січень + лютий + березень;
- і так далі до кінця року;
- з початком наступного року сума починає рахуватись з нуля;
Попередній абзац на DAX виглядає так:
Total Revenue YTD2 =
VAR maxDate = MAX(dim_Date[Date])
VAR yearStart = DATE( YEAR(maxDate), 1, 1)
RETURN
CALCULATE(
[Total Revenue],
dim_Date[Date] >= yearStart,
dim_Date[Date] <= maxDate
)- У змінну maxDate ми зберігаємо останню дату в поточному контексті – в матриці з розбивкою по місяцях це буде останній день кожного місяця.
- У змінній yearStart ми записуємо початок року в поточному контексті. Функція DATE приймає 3 аргументи – рік, місяць і день. Рік ми забираємо за допомогою YEAR з першої змінної, а перший день року – це ж завжди перший місяць і перший день.
- У RETURN ми повертаємо функцію CALCULATE з нашою звичною мірою доходу, а в аргументах фільтра “кажемо“ рахувати суму доходу за період між початком року до поточної дати в розбивці віжуалу.
Результат обох мір однаковий:
Але знаючи другий підхід, ви зможете порахувати це навіть коли дані у вас не з початку року. Був кейс, коли дохід в CRM почали фіксувати в лютому, а план розробили тільки в березні. Задача була – порівнювати план / факт з накопичувальним підсумком за рік. TOTALYTD рахував би некоректно – план з березня, факт з лютого. Таке можна порахувати тільки підходом, подібним другого, а не лаконічними функціями, створеними для ідеальних моделей даних.
Рахуємо "total" за попередній рік
Познайомлю вас ще з однією функцією – PARALLELPERIOD. У неї 3 аргументи – список дат календаря, число періоду зміщення дат, інтервал зміщення.
Використаємо цю функцію в мірі:
Total Revenue PRY =
CALCULATE(
[Total Revenue],
PARALLELPERIOD(dim_Date[Date], -1, YEAR)
)Напевно ви помітили, що це дуже схоже на DATEADD ( dim_Date[Date], -1, YEAR ), але є одна велика відмінність – PARALLELPERIOD повертає суму за обраний інтервал. Ми вказали мінус 1 рік, тому отримаємо суму доходу за весь попередній рік, незалежно від розбивки по місяцях:
Виводимо відсоток доходу від попереднього року
Так, ми вже маємо все необхідне для цього, тому залишилось вивести тільки відсоток: ділимо міру з накопичувальним підсумком на тотал попереднього року.
Total Revenue PRY % = DIVIDE([Total Revenue YTD], [Total Revenue PRY])
Зазвичай добре, коли на 100% ми виходимо десь в жовтні-листопаді. Подальші відсотки – це наш приріст за рік. Як на скріні вище – в 2023-му ми отримали на 5% більше, ніж в 2022-му.
Візуалізуємо розрахунки
Звісно, що в нудних таблицях і аналізувати нудно, тому давайте це візуалізуємо. У стовпчиковій діаграмі будемо порівнювати рік-до-року по місяцях, а на графіку виведемо розрахунки з накопичувальним підсумком за цей рік і попередній.
До речі, накопичувальний розрахунок за попередній рік теж дуже простий – достатньо використати відповідну міру:
Total Revenue PYTD =
TOTALYTD(
[Total Revenue PY],
dim_Date[Date]
)
На віжуалі Year-over-Year використана міра Total Revenue YOY %.
На віжуалі Year-to-Date – Total Revenue YTD і Total Revenue PYTD.
Висновок
У цій статті ми розібрали дуже корисні розрахунки, які допомагають зрозуміти річні тенденції як в розрізі місяців, що враховує й сезонність, так і в розрізі тоталу, від початку року.
Я думаю, ви переконались, що порахувати це в Power BI доволі просто та швидко, при цьому цінність таких інсайтів для бізнесу складно переоцінити. Сподіваюсь, ви знайдете застосування таким підходам у своїх звітах.
І як завжди – залишайте питання та коментарі нижче. Також пишіть про що вам було б цікаво та корисно почитати ще – можливо наступну статтю я напишу саме для вас)
Завантаження коментарів…