УДК 004.42DAX
ББК 32.97
Ф43
Ф43 Альберто Феррари, Марко Руссо
Шаблоны DAX. Наиболее полное собрание готовых к использованию решений
на языке DAX для Power BI, Analysis Services и Power Pivot / пер.
с англ. А. Ю. Гинько. – М.: ДМК Пресс, 2021. – 408 с.: ил.
ISBN 978-5-97060-909-5
Данная книга предназначена для разработчиков, уже знакомых с
языком DAX и желающих повысить свою квалификацию, используя
представленные здесь шаблоны. В большинстве из них применяются
продвинутые техники DAX, которые читателю следует освоить и использовать
в своих рабочих сценариях. Авторы уделяют пристальное
внимание сценариям с использованием функций логики операций со
временем, а также рассмат ривают большое количество действительно
полезных шаблонов, наиболее часто встречающихся на практике.
Для каждого шаблона предоставляются демонстрационные файлы в
формате Power BI и Power Pivot для Excel. Иногда версии кода незначительно
отличаются. Предпочтение отдается программному продукту
Power BI, в котором на момент написания книги реализованы все актуальные
новинки DAX. Рассматриваемые шаблоны проверены в июньской
версии Power BI 2020 года, Excel 2019 и Excel для Microsoft 365 версии
2006.
Авторы книги – одни из ведущих специалистов в области бизнесанали
тики, основавшие сайт SQLBI.com, на котором регулярно публикуются
статьи по DAX и другим инструментам Microsoft, и выпустившие
«Подробное руководство по DAX» (издание на русском языке – М.: ДМК
Пресс, 2021) для начинающих пользователей.
Publisher / Editorial Production: SQLBI Corp., Las Vegas, NV, Unites States. Copyright © 2020
by Alberto Ferrari and Marco Russo. All rights reserved. © 2021 by DMK Press. All rights reserved.
Все права защищены. Любая часть этой книги не может быть воспроизведена в какой
бы то ни было форме и какими бы то ни было средствами без письменного разрешения
владельцев авторских прав.
Материал, изложенный в данной книге, многократно проверен. Но, поскольку вероятность
технических ошибок все равно существует, издательство не может гарантировать
абсолютную точность и правильность приводимых сведений. В связи с этим издательство
не несет ответственности за возможные ошибки, связанные с использованием книги.
ISBN 978-1-7353652-0-6 (англ.)
ISBN 978-5-97060-909-5 (рус.)
© Alberto Ferrari, Marco Russo, 2020
© Оформление, перевод на русский язык,
издание, ДМК Пресс, 2021
Стр.5
Оглавление
Предисловие от издательства .......................................................... 11
Введение ............................................................................................ 12
Глава 1. Вычисления, связанные со временем ............................... 18
Глава 2. Стандартные вычисления на основе времени ................. 23
Введение в вычисления на основе времени ...................................................24
Стандартные функции логики операций со временем .......................................25
Отключение автоматической даты и времени ....................................................27
Ограничения функций логики операций со временем .......................................27
Создание таблицы дат ............................................................................................28
Управление визуализациями для будущих дат ....................................................30
Соглашение об именованиях ................................................................................30
Вычисление нарастающих итогов ...................................................................32
Нарастающие итоги с начала года ........................................................................33
Нарастающие итоги с начала квартала ................................................................35
Нарастающие итоги с начала месяца ...................................................................36
Сравнение периодов ........................................................................................38
Годовое сравнение .................................................................................................38
Квартальное сравнение .........................................................................................40
Месячное сравнение ..............................................................................................42
Сравнение периодов ..............................................................................................43
Сравнение нарастающих итогов .....................................................................45
Годовое сравнение нарастающих итогов .............................................................45
Квартальное сравнение нарастающих итогов .....................................................47
Месячное сравнение нарастающих итогов ..........................................................49
Сравнение нарастающих итогов с полным предыдущим периодом ...........50
Сравнение нарастающих итогов за год с полным прошлым годом ...................51
Сравнение нарастающих итогов за квартал с полным прошлым кварталом ....54
Сравнение нарастающих итогов за месяц с полным прошлым месяцем ..........56
Вычисление скользящей годовой суммы .......................................................58
Скользящая годовая сумма ....................................................................................58
Сравнение скользящих годовых сумм ..................................................................59
Скользящее среднее .........................................................................................61
Скользящее среднее за 30 дней .............................................................................62
Скользящее среднее за три месяца .......................................................................63
Скользящее среднее за год ....................................................................................64
Фильтрация других атрибутов даты ...............................................................65
Глава 3. Вычисления на основе месяца ........................................... 69
Введение в вычисления на основе месяца .....................................................70
Создание таблицы дат ............................................................................................70
Соглашение об именованиях ................................................................................75
Стр.6
6 Оглавление
Вычисление нарастающих итогов ...................................................................77
Нарастающие итоги с начала года ........................................................................77
Нарастающие итоги с начала квартала ................................................................78
Сравнение периодов ........................................................................................79
Годовое сравнение .................................................................................................80
Квартальное сравнение .........................................................................................81
Месячное сравнение ..............................................................................................83
Сравнение периодов ..............................................................................................84
Сравнение нарастающих итогов .....................................................................86
Годовое сравнение нарастающих итогов .............................................................86
Квартальное сравнение нарастающих итогов .....................................................88
Сравнение нарастающих итогов с полным предыдущим периодом ...........89
Сравнение нарастающих итогов за год с полным прошлым годом ...................90
Сравнение нарастающих итогов за квартал с полным прошлым кварталом ....91
Вычисление скользящей годовой суммы .......................................................93
Скользящая годовая сумма ....................................................................................93
Сравнение скользящих годовых сумм ..................................................................94
Скользящее среднее .........................................................................................96
Скользящее среднее за три месяца .......................................................................96
Скользящее среднее за год ....................................................................................97
Работа с годами, содержащими больше 12 месяцев ......................................97
Глава 4. Вычисления на основе недели ..........................................100
Введение в вычисления на основе недели ....................................................101
Создание таблицы дат ..........................................................................................101
Фильтрозащищенные столбцы ...........................................................................104
Управление визуализациями для будущих дат ..................................................105
Соглашение об именованиях ..............................................................................106
Вычисление нарастающих итогов .................................................................108
Нарастающие итоги с начала года ......................................................................109
Нарастающие итоги с начала квартала ..............................................................111
Нарастающие итоги с начала месяца .................................................................112
Нарастающие итоги с начала недели ..................................................................114
Сравнение периодов ......................................................................................115
Годовое сравнение ...............................................................................................115
Квартальное сравнение .......................................................................................117
Недельное сравнение ...........................................................................................119
Сравнение периодов ............................................................................................121
Сравнение нарастающих итогов ...................................................................123
Годовое сравнение нарастающих итогов ...........................................................123
Квартальное сравнение нарастающих итогов ...................................................125
Недельное сравнение нарастающих итогов .......................................................127
Сравнение нарастающих итогов с полным предыдущим периодом .........130
Сравнение нарастающих итогов за год с полным прошлым годом .................130
Сравнение нарастающих итогов за квартал с полным прошлым кварталом .... 131
Сравнение нарастающих итогов за неделю с полной прошлой неделей .........133
Стр.7
Оглавление 7
Вычисление скользящей годовой суммы .....................................................135
Скользящая годовая сумма ..................................................................................135
Сравнение скользящих годовых сумм ................................................................136
Скользящее среднее .......................................................................................138
Скользящее среднее за четыре недели ...............................................................138
Скользящее среднее за квартал ..........................................................................140
Скользящее среднее за год ..................................................................................141
Глава 5. Пользовательские вычисления, связанные
со временем ......................................................................................143
Введение в пользовательские вычисления, связанные со временем .........144
Создание таблицы дат ..........................................................................................145
Фильтрозащищенные столбцы ...........................................................................148
Управление визуализациями для будущих дат ..................................................150
Соглашение об именованиях ..............................................................................151
Вычисление нарастающих итогов .................................................................153
Нарастающие итоги с начала года ......................................................................153
Нарастающие итоги с начала квартала ..............................................................156
Нарастающие итоги с начала месяца .................................................................157
Сравнение периодов ......................................................................................158
Годовое сравнение ...............................................................................................158
Квартальное сравнение .......................................................................................161
Месячное сравнение ............................................................................................163
Сравнение периодов ............................................................................................164
Сравнение нарастающих итогов ...................................................................166
Годовое сравнение нарастающих итогов ...........................................................166
Квартальное сравнение нарастающих итогов ...................................................168
Месячное сравнение нарастающих итогов ........................................................171
Сравнение нарастающих итогов с полным предыдущим периодом .........174
Сравнение нарастающих итогов за год с полным прошлым годом .................174
Сравнение нарастающих итогов за квартал с полным прошлым кварталом .. 176
Сравнение нарастающих итогов за месяц с полным прошлым месяцем ........178
Вычисление скользящей годовой суммы .....................................................179
Скользящая годовая сумма ..................................................................................180
Сравнение скользящих годовых сумм ................................................................181
Скользящее среднее .......................................................................................184
Скользящее среднее за 30 дней ...........................................................................184
Скользящее среднее за три месяца .....................................................................186
Скользящее среднее за год ..................................................................................187
Глава 6. Сравнение разных временных интервалов ....................189
Описание шаблона .........................................................................................189
Глава 7. Полуаддитивные вычисления ...........................................193
Введение ..........................................................................................................194
Первая и последняя даты ...............................................................................195
Первая и последняя даты с данными ............................................................196
Стр.8
8 Оглавление
Первая и последняя даты по клиенту ...........................................................198
Остатки на начало и конец периода .............................................................200
Рост за период .................................................................................................204
Глава 8. Нарастающие итоги ...........................................................208
Базовый сценарий ..........................................................................................208
Нарастающие итоги по столбцам с сортировкой .........................................211
Глава 9. Таблица параметров ..........................................................213
Изменение единиц измерения меры ............................................................213
Множество независимых параметров ..........................................................216
Множество зависимых параметров ..............................................................217
Динамический выбор N лидирующих товаров ............................................219
Глава 10. Статическая сегментация ................................................222
Базовый шаблон .............................................................................................222
Диапазоны цен по категориям ......................................................................226
Диапазоны цен в объемных таблицах ..........................................................228
Глава 11. Динамическая сегментация ............................................230
Базовый шаблон .............................................................................................230
Кластеризация по изменению продаж .........................................................234
Кластеризация по лучшему статусу ..............................................................236
Глава 12. ABC-анализ ........................................................................239
Статический ABC-анализ ...............................................................................240
Снимок ABC-анализа ......................................................................................242
Динамический ABC-анализ ...........................................................................247
Поиск категории ABC .....................................................................................249
Глава 13. Новые и постоянные покупатели ...................................252
Введение ..........................................................................................................253
Описание шаблона .........................................................................................257
Внутренние меры .................................................................................................260
Внешние меры ......................................................................................................261
Как использовать меры из шаблона ...................................................................261
Динамический относительный шаблон ........................................................263
Внутренние меры .................................................................................................263
Новые покупатели ................................................................................................265
Ушедшие покупатели ...........................................................................................266
Временно ушедшие покупатели .........................................................................267
Вернувшиеся покупатели ....................................................................................269
Постоянные покупатели ......................................................................................270
Динамический абсолютный шаблон .............................................................271
Внутренние меры .................................................................................................272
Новые покупатели ................................................................................................273
Ушедшие покупатели ...........................................................................................274
Стр.9
Оглавление 9
Временно ушедшие покупатели .........................................................................275
Вернувшиеся покупатели ....................................................................................276
Постоянные покупатели ......................................................................................277
Обобщенный динамический шаблон (по категории) ..................................278
Внутренние меры .................................................................................................280
Новые покупатели ................................................................................................281
Ушедшие покупатели ...........................................................................................284
Временно ушедшие покупатели .........................................................................285
Вернувшиеся покупатели ....................................................................................287
Постоянные покупатели ......................................................................................290
Использование снимков ................................................................................292
Создание производных снимков в DAX ..............................................................296
Глава 14. Количество уникальных связанных элементов ............301
Описание шаблона .........................................................................................301
Глава 15. Незавершенные события .................................................306
Определение незавершенных событий ........................................................306
Открытые заказы ............................................................................................309
Открытые заказы с использованием снимков .............................................313
Глава 16. Ранжирование ..................................................................318
Статическое ранжирование ...........................................................................318
Динамическое ранжирование .......................................................................320
Три лидирующих товара в категории ...........................................................322
Глава 17. Иерархии ...........................................................................325
Определение текущего уровня иерархии .....................................................325
Доля от родительского уровня .......................................................................327
Глава 18. Иерархии типа родитель–потомок ................................329
Введение ..........................................................................................................329
Базовый шаблон иерархии типа родитель–потомок ...................................331
Иерархия ведомости.......................................................................................336
Шаблон безопасности для иерархии родитель–потомок ............................343
Глава 19. Сравнение сопоставимых показателей .........................345
Введение ..........................................................................................................345
Сопоставимые продажи по магазинам с помощью снимков ......................347
Сопоставимые продажи по магазинам без помощи снимков ....................350
Глава 20. Матрица переходов ..........................................................352
Введение ..........................................................................................................352
Статическая матрица переходов ...................................................................355
Динамическая матрица переходов ...............................................................358
Глава 21. Опросник ...........................................................................362
Описание шаблона .........................................................................................362
Стр.10
10 Оглавление
Глава 22. Анализ покупательской корзины ...................................368
Определение метрик ассоциативных правил ..............................................369
Простые отчеты ..............................................................................................371
Базовый пример шаблона ..............................................................................373
Оптимизированный пример шаблона ..........................................................379
Глава 23. Пересчет курсов валют ....................................................382
Несколько валют источника, одна валюта отчета ........................................383
Одна валюта источника, несколько валют отчета ........................................386
Несколько валют источника, несколько валют отчета .................................389
Глава 24. Прогнозирование .............................................................392
Введение ..........................................................................................................392
Модель данных ...............................................................................................393
Бизнес-требования .........................................................................................396
Прогноз на основании предыдущего года .........................................................396
Вышедшие из оборота товары не учитываются в прогнозе .............................396
У новых товаров есть свой прогноз ....................................................................397
Товары добавляются и удаляются на ежегодной основе ...................................397
Прогнозирование ...........................................................................................398
Фактические данные и прогноз на одной диаграмме .................................401
Предметный указатель ....................................................................406
Стр.11