Объединение функций Time Intelligence для иллюстрации логики применения фильтров DAX и управления ими
Делиться
Введение
Начнем с простой таблицы:
Каждая строка в матрице отображает общий объем онлайн-продаж за каждый месяц.
Все идет нормально.
Интерпретация заключается в том, что мы видим общий объем онлайн-продаж, отфильтрованный по месяцам.
Но это не вся правда.
Давайте посмотрим на модель данных:
Если вы посмотрите внимательно, то увидите, что между двумя столбцами дат выстроена связь.
Связи со столбцом «Месяц» нет.
Если мы пойдем этим путем, то приведенная выше интерпретация будет не совсем точной.
Полная интерпретация должна быть такой: каждая строка показывает общий объём онлайн-продаж, отфильтрованный по таблице дат. Строки таблицы дат сгруппированы по месяцам. Каждая строка показывает общий объём продаж за все дни каждого месяца.
Когда мы осознаем эту деталь, мы станем на шаг ближе к пониманию DAX в целом и функций временной аналитики в частности.
Давайте сделаем еще один шаг вперед.
YTD и базовый запрос
Теперь давайте добавим показатель YTD, чтобы посмотреть, что произойдет:
Мера не представляет собой ничего особенного, и результаты легко понять.
Теперь давайте посмотрим, что именно делает функция DATESYTD().
В пояснении от dax.guide говорится: «Возвращает набор дат в году до последней даты, видимой в контексте фильтра».
Что именно это означает?
Чтобы разобраться в этом вопросе, давайте сначала напишем запрос DAX, чтобы получить список дат в июне 2024 года, как это сделано в визуализации выше:
DEFINE VAR YearFilter = TREATAS({ 2024 }, 'Дата'[Год]) VAR MonthFilter = TREATAS({ 6 }, 'Дата'[Месяц]) EVALUATE SUMMARIZECOLUMNS('Дата'[Дата],YearFilter,MonthFilter)
В результате получается список из 30 дней июня:
Это фильтр, примененный к строке за июнь 2024 г. в матрице, показанной выше.
Какой результат получим, если применить функцию DATESYTD() к результату?
Вот запрос:
DEFINE VAR YearFilter = TREATAS({ 2024 }, 'Дата'[Год]) VAR MonthFilter = TREATAS({ 6 }, 'Дата'[Месяц]) VAR BasisDates = CALCULATETABLE( SUMMARIZECOLUMNS('Дата'[Дата],YearFilter,MonthFilter) ) VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Дата'[Дата]) ) EVALUATE YTDDates
И вот, результат:
Это список из 182 строк, содержащий все даты с начала года до последнего дня июня 2024 года.
Это определение YTD.
Если взглянуть на следующую меру:
Онлайн-продажи (YTD) = VAR YTDDates = DATESYTD('Дата'[Дата]) RETURN CALCULATE([Сумма онлайн-продаж] ,YTDDates )
Мы понимаем, что переменная YTDDates — это «всего лишь» список дат, примененный в качестве фильтра к функции CALCULATE().
Это ключ ко всем функциям интеллекта Времени.
Вернитесь на год назад — несколько примеров
Что произойдет, если к результату применить другую функцию?
Например, SAMEPERIODLASTYEAR()?
Чтобы ответить на этот вопрос, я использую следующий запрос DAX:
DEFINE VAR YearFilter = TREATAS({ 2024 }, 'Дата'[Год]) VAR MonthFilter = TREATAS({ 6 }, 'Дата'[Месяц]) VAR BasisDates = CALCULATETABLE( SUMMARIZECOLUMNS('Дата'[Дата],YearFilter,MonthFilter ) ) VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Дата'[Дата]) ) VAR YTDDatesPY = SAMEPERIODLASTYEAR(YTDDates) EVALUATE YTDDatesPY
Я намеренно отделил вызов SAMEPERIODLASTYEAR() от DATESYTD() для удобства чтения. Можно было бы вложить DATESYTD() в SAMEPERIODLASTYEAR().
На этот раз у нас 181 строка, поскольку 2024 год был високосным.
И даты сдвинуты на один год назад:
Итак, снова, когда мы применяем функцию Time Intelligence к мере, функция, например, DATESYTD(), возвращает список дат.
Обратите внимание: при применении фильтра все существующие фильтры в таблице дат удаляются.
Пользовательская логика
Теперь давайте применим эти знания к пользовательской логике временного интеллекта.
Для начала немного изменим фильтр по году и месяцу:
DEFINE VAR YearMonthFilter = TREATAS({ 202406 }, 'Дата'[MonthKey]) EVALUATE SUMMARIZECOLUMNS('Дата'[Date] , YearMonthFilter )
Результат этого запроса такой же, как и в начале статьи.
На этот раз я установил фильтр с числовым значением в столбце [MonthKey].
Как мне вернуться к предыдущему году?
Если подумать математически, то это просто вычитание 100:
202406 – 100 = 202306
Давайте попробуем:
То же самое можно сделать и с другими числовыми форматами.
Если взять, например, такой финансовый год: 2425 (для финансового года 24/25)
Вы можете вычесть 101, чтобы получить номер предыдущего финансового года: 2425 – 101 = 2324.
Другим примером пользовательской логики анализа времени является скользящее среднее значение, где для каждого дня мы вычисляем среднее значение за последние 10 дней:
Поскольку содержимое переменной DateRange снова представляет собой список дат, я могу применить к нему функцию SAMEPERIODLASTYEAR() и получить нужный мне результат:
DEFINE VAR YearFilter = TREATAS({ 2024 }, 'Дата'[Год]) VAR MonthFilter = TREATAS({ 6 }, 'Дата'[Месяц]) // 1. Получить первую и последнюю дату для текущего контекста фильтра VAR MaxDate = CALCULATE(MAX( 'Дата'[Дата] ) ,YearFilter ,MonthFilter ) VAR MinDate = CALCULATE( DATEADD( 'Дата'[Дата], — 10, ДЕНЬ ) ,'Дата'[Дата] = MaxDate ) // 2. Сгенерировать диапазон дат, необходимый для скользящего среднего (четыре месяца) VAR DateRange = CALCULATETABLE( DATESBETWEEN( 'Дата'[Дата] ,MinDate ,MaxDate ) ) EVALUATE ТОТ ЖЕПЕРИОДЛАСТГОД( Диапазон дат )
И вот результат:
Эта логика возвращает 11 строк, поскольку включает последний день месяца. В зависимости от требуемого результата нам необходимо скорректировать способ вычисления первой и последней дат в списке дат (фильтр, применяемый к показателю).
Конечно, это повторение того, что я показал выше. Однако это показывает, что тот же подход можно применять к различным сценариям.
Как только вы это поймете, ваша работа с функциями временной аналитики и другими функциями, принимающими в качестве входных данных таблицы значений, станет намного проще для понимания и освоения.
Заключение
Хотя для запросов я использовал DAX Studio, вы можете использовать те же запросы в инструменте запросов DAX в Power BI Desktop.
Я намеренно использовал эти запросы, чтобы продемонстрировать, что в DAX мы постоянно работаем с таблицами, даже если не всегда осознаем это.
Но это важная деталь, которая помогает нам в понимании DAX.
Хотя часть представленного здесь кода DAX может устареть с появлением новой функции анализа времени на основе календаря в Power BI, изложенные здесь принципы остаются актуальными. Такие функции, как DATESYTD() или SAMEPERIODLASTYEAR(), по-прежнему существуют и работают так же, как и раньше. В настоящее время в этом отношении ничего не изменится, поскольку описанные здесь концепции по-прежнему актуальны.
Ссылки
Как и в предыдущих статьях, я использую пример набора данных Contoso. Вы можете бесплатно скачать набор данных ContosoRetailDW с сайта Microsoft здесь.
Данные Contoso можно свободно использовать по лицензии MIT, как описано в этом документе. Я изменил набор данных, чтобы сместить данные на современные даты.
Источник: towardsdatascience.com
