Каскадная диаграмма или Waterfall chart в Power BI – одна из самых популярных визуализаций для поиска факторов или причин отклонений показателей текущего года от предыдущего, или для сравнения плана и факта. Но настраивать эту диаграмму не всегда удобно. Обычно у нас есть таблица с данными за несколько лет, а при построении каскадной диаграммы периоды требуется ограничивать срезами или фильтрами. Иначе на ней будет много столбиков для дат.
Но что делать, если пользователи хотят выбирать в отчете только один год и сравнивать его с предыдущим? Обычный срез с одним выбранным годом в этой задаче не поможет. Что же делать? Все просто – идем на военную хитрость и добавляем в модель специальную таблицу. А заодно находим сопоставимые предыдущие даты, чтобы неполный текущий год корректно сравнивать с прошлым.
Как с помощью факторного анализа сравнить текущий и прошлый год?
Нас иногда спрашивают: как в Power BI построить каскадную диаграмму (Waterfall chart) для сравнения выбранного и предыдущего года. И можно ли при этом на срезе выбирать только один год, чтобы данные за прошлый год «подтягивались» сами?
Ответ: можно! Как именно — читайте дальше.
А чтобы вам было удобнее, приложили файл.
Шаг 1. Добавить срез для года
Чтобы настроить выбор года в отчете, потребуется отдельная таблица для среза, которая не будет фильтровать Календарь (таблицу дат). Такую таблицу можно создать с помощью формулы VALUES и затем добавить её в срез.
СрезГод = VALUES ( ' Календарь' [Year] ) |
Шаг 2. DAX-формула для Waterfall chart
Чтобы в каскадной диаграмме показать текущий и предыдущий год, потребуется мера:
KPI waterfall = CALCULATE ( [KPI], FILTER ( ' Календарь' ,[Year] = MAX ( ' СрезГод' [Year] ) ||[Year] = MAX ( ' СрезГод' [Year] ) -1) ) |
Шаг 3. Данные за сопоставимые периоды
Почти все готово. Но за текущий год у нас есть не все данные, только по сентябрь. Для его сравнения с предыдущим годом данные должны быть сопоставимыми – прошлый год тоже только по сентябрь. Для ограничения отображения периодов в отчетах используют разные способы. Например, вычисляемый столбец в таблице дат (Календаре):
DatesCY-PY = var _maxDataDate = MAX ( ' Данные' [дата] )var _maxYear = YEAR ( _maxDataDate ) var _prevYear = YEAR ( _maxDataDate ) -1 var _maxCompareDate = IF ( ' Календарь' [Year] = _prevYear,' Календарь' [Date] <= EOMONTH ( _maxDataDate, -12 ),' Календарь' [Date] <= _maxDataDate )return _maxCompareDate |
Осталось внести корректировки в формулу для Waterfall chart, чтобы сравнить текущий год с сопоставимыми данными прошлого года:
KPI waterfall = CALCULATE ( [KPI], FILTER ( ' Календарь' , [Year] = MAX ( ' СрезГод' [Year] ) )) + CALCULATE ( [KPI], FILTER ( ' Календарь' ,[Year] = MAX ( ' СрезГод' [Year] ) -1 && ' Календарь' [DatesCY-PY] = TRUE()) ) |
Шаг 4. Настройка оформления
Настраиваем диаграмму, срезы и заголовки. Готово!