Dax Pro Solutions

📰 Расчет показателей, где один из них должен иметь название недели, выбранного в слайсере периода в RS Данная задача решается намного проще в версиях PBI Desktop, которые поддерживают Field Parameters. Но в данном случае решение должно быть под RS. 1️⃣ Создаем требуемый справочник, который будет содержать названия всех недель и их порядок. (у нас уже есть поле сортировки, полученное через RANKX, его и используем), а так же всех названий показателей. Это позволит там получить далее в столбцах матрицы нужные столбцы.

Matrix Columns = 
UNION(
    SELECTCOLUMNS(
        ALLNOBLANKROW('Calendar'[ISO Week Sort],'Calendar'[ISO Week Name]),
        "ISO Week Sort",[ISO Week Sort],
        "Name","Qty  "&UNICHAR(10)&[ISO Week Name]
    ),
    CROSSJOIN(
        ALLNOBLANKROW('Calendar'[ISO Week Sort]),
        ROW("Name"," Sales Amount")
    )
)

2️⃣ Добавляем название недель из Calendar в слайсер, а названия из столбца справочника в столбцы. 3️⃣ Пишем меру, проверяющую текущее название столбца и выводящую требуемый результат расчета. Основное ограничение - у всех расчетов один тип данных, иначе каждому нужно назначать через функцию FORMAT, что приведет к типу TEXT:

Weeks Calc = 
SWITCH(
    LEFT(MAX('Matrix Columns'[Name]),4),
    " Sal",CALCULATE([Sales Amount],TREATAS(VALUES('Matrix Columns'[ISO Week Sort]),'Calendar'[ISO Week Sort])),
    "Qty ",CALCULATE(SUM(Sales[Quantity]),TREATAS(VALUES('Matrix Columns'[ISO Week Sort]),'Calendar'[ISO Week Sort]))
) -- пробел перед названием позволяет правильно сортировать название

👉🏻PBIX с детальным разбором во вложении👈🏻 RS Январь 22 + ( для облачной версии лучше использовать Fields Parameter)

📅 Переток недели в годах и месяцах. Часто случается, что неделя года может начинаться в декабре, а кончаться в первых числах января. При этом, такие недели на графиках могут давать неверную суммарную информацию в рамках года. То есть, если я выберу 2023 год в срезе, а потом выберу неделю с 26.12.22 по 01.01.23, то значение будет считаться только за вхождение дней 2023 года, а именно за 01.01.23. ✅ Последовательность действий для решения: 1) делаем разбивку лет, соответствующих ISO

YEAR([Date]+(3-WEEKDAY( [Date],3)))

2) делаем разбивку недель ISO

WEEKNUM([Date],21)

3) пишем меру, позволяющую сравнивать однотипные периоды (ISO недели) в текущем и предыдущем годах:

CALCULATE(
    [Sales Amount],
    'Calendar'[ISO Year]=MAX('Calendar'[ISO Year])-1,
--двигаем период дат на ISO год назад
    ALL('Calendar'),
    VALUES('Calendar'[ISO Week])
-- получаем в текущем контексте список недель
)

👉🏻PBIX с детальным разбором во вложении👈🏻 RS Январь 22 +

📅 Кастомный формат недели по ISO. Есть несколько подходов к созданию недель в DAX: 1️⃣ С помощью текста и сортировки. Рассчитываем даты начала и конца недели ISO и приводим через функцию FORMAT методом объединения к нужному формату:

 "ISO Week Name",  
    COMBINEVALUES(
        " - ",
        FORMAT([Date]-WEEKDAY([Date],2)+1,"""W""ww:  dd.mm","ru_ru"),
        FORMAT([Date]+7-WEEKDAY([Date],2),"dd.mm")
    )

Сквозное ранжирование год-неделя ISO для обратной сортировки недель в слайсере:

"ISO Week Sort",RANKX(_dates,YEAR([Date]+(3-WEEKDAY( [Date],3)))*100+WEEKNUM([Date],21),,DESC,Dense)

2️⃣ С помощью типа "Дата и время" и формата столбца.

"ISO Date Weeks",[date]+TIME(0,00,WEEKNUM([Date],21)),    -- Берем текущую дату и прибавляем количество секунд, равное номеру недели, рассчитанной для разбивки ISO и приводим к формату "W"s dd mmm yy                           
"ISO Weeks AS Date",  -- Хочу предупредить, что данное решение имеет ограничения для дат до 2000х годов, из-за приведения DateTime к формату "W"yy: dd.mm - nn.ss и Вы сами должны принимать решение стоит ли его брать в проекты
    VAR _StartDate = [Date]-WEEKDAY([Date],2)+1 -- Находим дату начала ISO недели
    VAR _EndDate =   [Date]+7-WEEKDAY([Date],2) -- Находим дату окончания ISO недели
    VAR _year = (YEAR([Date])-2000)*100+WEEKNUM([Date],21)  -- Берем год даты начала недели, вычитаем 2000 и умножаем на 100, затем прибавляем номер недели ISO. В итоге получаем 2401 и т.д., что позволяет правильно сортировать даты

и в итоге, в RETURN мы можем вывести один из двух вариантов отображения недели:

    RETURN
V1 DATE(_year,MONTH(_StartDate),DAY(_StartDate))+TIME(0,DAY(_EndDate),MONTH(_EndDate))
-- Приводим год, месяц, день и минутам, секунд к требуемому DateTime  и назначаем пользовательский формат "W"yy: dd.mm - nn.ss.
V2 _StartDate+TIME(0,DAY(_EndDate),MONTH(_EndDate))
-- Формат имеет вид     dd.mm - nn.ss Нет проблем с годами, но нет и номера недели

👉🏻PBIX с детальным разбором во вложении👈🏻 RS Январь 22 +

📅 Пасхальные недели в календаре Для того, чтобы сравнить продажи за фиксированный период 4 недели до Пасхи и одна неделя после за разные года , нам потребуется: 1️⃣ Сделать требуемую разбивку в календаре. Она позволит нам не привязываясь к датам сравнивать продажи за релевантные (однотипные) периоды, но не совпадающие именно по датам (то есть сдвиг с помощью функций TI невозможен). 2️⃣ Привести разбивку к понятному для пользователя формату. Разбивка имеет вид -4...1, пропишем код в панели формата столбца:

#0. "Week later";#0. "Weeks ago";"Easter Week"

3️⃣ Добавить фильтр на страницу отчета, отсекающий периоды, не имеющие разбивку. Для этого помещаем столбец Easter в панель фильтров страницы и устанавливаем значение "Не является пустым". Можно писать меры с учетом разбивки и не будет требоваться данный фильтр, но тогда и в слайсере Easter Weeks будут присутствовать BLANK строки, что все равно потребует наложения фильтра. 👉🏻PBIX с детальным разбором во вложении👈🏻 RS Январь 22 +

📅 Сравнение периодов в Power BI позволяет анализировать данные за разные временные интервалы и выявлять тренды и паттерны поведения показателей. Я подготовил для вас решения трех кейсов: 1️⃣ Аналогичный по длительности предыдущий период 2️⃣ Сравнение закрытого периода с N месяцев назад 3️⃣ Сравнение выбранного периода и последних N рабочих дней 👉🏻 PBIX с детальным разбором в комментарии 👈🏻 Подходит на RS январь 23 +

📅 Календарь в DAX Почему именно такой подход я выбрал и какая его польза. ⭕️ Проблемы других календарей. Основная - присутствие дополнительных целочисленных столбцов для верной сортировки и их аналогов в текстовом виде, приведенных к требуемому формату (202201 и "Янв - 2022"). Это ведет к увеличению самого календаря и последующим усложнениям в написании мер, так как всегда нужно помнить что помимо столбца с которого мы сбрасываем контекст фильтра есть его целочисленный аналог, используемый для сортировки. ✅ Используйте тип хранения "Дата". Когда Вы приводите все к датам, а форматы к требуемым Вам (как на примере слева) - эти ограничения не действуют и последующие расчеты становятся намного предсказуемее и проще. Плюс Вам не нужно помнить настроили ли Вы сортировку текстовых полей или нет.

В примере, я добавил несколько полей, приведенных к True/False для того чтобы было проще ссылаться на них при расчете и сравнении текущего квартала или месяца с любым другим аналогичным периодом.

Разметка в поле Closed Period сильно упрощает последующие расчеты для сравнения совершенных событий с аналогичным периодом (к примеру сравнивать 3 дня января 2024 с 3 днями прошлого месяца или 3 днями января прошлого года). Поле Day of Year служит так же для сравнения выбранного периода/рабочих дней в году с соответствующим днями в прошлом. 👉🏻 PBIX с детальным разбором в комментарии 👈🏻 Подходит на RS январь 23 +

Полезные ссылки и литература 📚 Книги Analyzing Data with Power BI and Power Pivot for Excel (Business Skills) 1st Edition Pro Power BI Architecture: Development, Deployment, Sharing, and Security for Microsoft Power BI Solutions 2nd ed. Edition The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition (Business Skills) Exam Ref PL-300 Power BI Data Analyst 1st Edition Exam Ref DP-500 Designing and Implementing Enterprise-Scale Analytics Solutions Using Microsoft Azure and Microsoft Power BI 1st Edition Pro Power BI Desktop: Self-Service Analytics and Data Visualization for the Power User 3rd ed. Edition (немного устарела, но отличные основы) Microsoft Power BI Performance Best Practices: A comprehensive guide to building consistently fast Power BI solutions Expert Data Modeling with Power BI: Get the best out of Power BI by building optimized data models for reporting and business needs Introducing Charticulator for Power BI: Design Vibrant and Customized Visual Representations of Data 1st ed. Edition Microsoft Power Platform Enterprise Architecture: A guide for architects and decision makers to craft complex solutions tailored to meet business needs 1st Edition, Kindle Edition Extreme DAX: Take your Power BI and Microsoft data analytics skills to the next level Microsoft Power BI Cookbook: Gain expertise in Power BI with over 90 hands-on recipes, tips, and use cases, 2nd Edition 2nd ed. Edition Pro Power BI Theme Creation: JSON Stylesheets for Automated Dashboard Formatting 2nd Edition, Kindle Edition Pro DAX and Data Modeling in Power BI: Creating the Perfect Semantic Layer to Drive Your Dashboard Analytics 1st ed. Edition Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence 1st ed. Edition Up and Running with DAX for Power BI: A Concise Guide for Non-Technical Users 1st ed. Edition Pro DAX with Power BI: Business Intelligence with PowerPivot and SQL Server Analysis Services Tabular 1st ed. Edition, Kindle Edition 🖥 Полезные сайты и блоги https://powerbiweekly.info/ https://pbidax.wordpress.com/ Все про DAX https://www.sqlbi.com/articles/ https://www.daxpatterns.com/ https://data-goblins.com/articles http://mdxdax.blogspot.com/ Информация не обновляется, но интересно почитать https://www.linkedin.com/pulse/multi-color-gradient-dax-power-bi-%C5%A1t%C4%9Bp%C3%A1n-re%C5%A1l Про цвета и градиенты в DAX https://www.red-gate.com/simple-talk/blogs/ Про Power BI, Data Modeling, Fabric и не только https://xxlbi.com/ https://blog.gbrueckl.at/ https://thebipower.fr/ https://themes.powerbi.tips/themes/wireframes https://www.pbiexplorer.com/ https://www.elegantbi.com/blog https://deneb-viz.github.io/ https://gist.github.com/johnmackintosh/47eb03f3994eea99b4b7db05f6f0c947 https://powerbidocs.com/ https://www.oliviertravers.com/ https://anyonconsulting.com/business_intelligence/which-one-to-use-power-bi-desktop-or-power-bi-report-server-or-power-bi-service/ https://goodly.co.in/blog/ https://kohera.be/blog/ https://eriksvensen.wordpress.com/ https://pbi-guy.com/blog/ https://sqlserverbuilds.blogspot.com/2021/04/power-bi-report-server-versions.html https://zenatti.net/author/sabrinazenatti/ https://docs.sqlbi.com/bravo/ https://blog.crossjoin.co.uk/category/dax/ https://easybi.be/ https://dotnet.github.io/docfx/docs/basic-concepts.html 🎥 Каналы на YouTube https://www.youtube.com/@RADACAD https://www.youtube.com/@PowerBITips https://www.youtube.com/@MicrosoftPowerBI https://www.youtube.com/@GuyInACube 🛢 Примеры баз данных https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks https://github.com/sql-bi/Contoso-Data-Generator/releases/tag/v1.0.0 https://github.com/awesomedata/awesome-public-datasets#finance https://services.odata.org/

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

Periods For Select = 
VAR _TodayDate = TODAY()
VAR _Today = 
            ROW(
                "ID",1,
                "Period","Сегодня",
                "Date",_todaydate
            )
...// иные строки периодов
RETURN
UNION(
    _CurrentWeek,
    _Today,
    _Yesterday
)

Данная вычисляемая таблица Periods For Select будет рассчитываться при каждом обновлении и периоды в разметке будут сдвигаться. 🔴 Ограничение такого подхода - работа всех мер, связанных с периодами и их сдвигами. Нужно быть очень аккуратными при написании и проверять все ли верно сдвигается, т.к. на календарь оказывается внешняя фильтрация. ✅ Подходит для RS May 23 + 👉🏻PBIX внутри 👈🏻

Хотите решения вашей задачи?