DAX Временная аналитика: DATEADD, TOTALYTD и месяц, которого нет

1. Почему таблица дат не является обязательной

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

Четыре, а не пять. В апреле нет заказов, поэтому в нем нет строк, поэтому у него нет значения месяца, поэтому он никогда не отображается. Это не особенность Power BI, это то, что группировка делает в каждом инструменте, и это тот же молчаливый пробел, который был скрыт в Месяц за месяцем в SQL и в Excel.

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

• Он должен иметь столбец типа данных дата или дата/время.
• Столбец даты должен содержать уникальные значения.
• Столбец даты не должен содержать ПУСТЫ.
• В столбце даты не должно быть пропущенных дат.
• Столбец даты должен охватывать полные годы. Год не обязательно является календарным годом.
• Для классической логики времени таблица должна быть помечена как таблица дат.

Прочтите четвертую и пятую из них вместе, и причина станет очевидной. Временной интеллект работает путем сдвига набора дат назад или вперед. Если дата, на которую он хочет перейти, отсутствует в таблице, он не может приземлиться, а если таблица останавливается на полпути через год, расчетам с начала года нечего накапливать.

Скажите вслух, почему «охватить полные годы» — это требование, а не предложение. Для измерения марта за текущий год необходимо, чтобы существовали январь и февраль, даже если в них ничего не продавалось, а для сравнения за январь 2026 года за предыдущий год необходимо представить весь 2025 год, даже если бизнес начался в июне.

2. Создание и разметка

Перед кодом: вам нужна каждая дата от начала ваших данных до конца. Прежде чем читать дальше, скажите, откуда должны взяться эти две даты.

Из данных, поэтому таблица расширяется при поступлении новых строк. Это делают две функции DAX, и я использую вторую.

-- Option 1: you control the range
Date =
CALENDAR ( DATE ( 2026, 1, 1 ), DATE ( 2026, 12, 31 ) )

-- Option 2: the range follows the model, and always covers full years
Date = CALENDARAUTO ()

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

Затем добавьте столбцы, по которым вы фактически будете группировать. Это вычисляемые столбцы таблицы дат, вычисляемые один раз при обновлении.

Year        = YEAR ( 'Date'[Date] )
Month       = FORMAT ( 'Date'[Date], "mmm yyyy" )
Month Sort  = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] )
Quarter     = "Q" & ROUNDUP ( MONTH ( 'Date'[Date] ) / 3, 0 )

Три завершающих шага, которые люди пропускают, и каждый из которых вызывает определенную ошибку.

1. Сортировка по месяцам Сортировка. Выберите столбец «Месяц», затем «Инструменты столбца», «Сортировать по столбцу». Без него ваша ось будет читать апрель, февраль, январь, март, май в алфавитном порядке, и это произойдет на первой диаграмме, которую вы построите.
2. Свяжите это с таблицей фактов. Один ко многим из 'Date'[Date] к Orders[OrderDate], однонаправленное, от даты до заказа. Таблица дат — это измерение, а таблица заказов — это факт, который представляет собой форму. руководство по звездообразной схеме это о.
3. Отметить как таблицу дат. Щелкните таблицу правой кнопкой мыши, выберите «Отметить как таблицу дат», выберите столбец даты. Требуется для классической разведки времени и бесплатен.

Одна вещь, которую нужно выключить, пока вы там. Параметр «Автоматическая дата/время» Power BI незаметно создает скрытую таблицу дат позади каждого столбца даты в вашей модели. Это удобно для быстрого просмотра и не является единым общим календарем, поэтому фильтры из него не распространяются на другие ваши таблицы. При наличии реальной таблицы дат это излишне и приводит к раздуванию модели.

3. С начала года с TOTALYTD

Перед мерой: вам нужен доход, накопленный с начала года. Предскажите, что он покажет в марте, учитывая приведенные выше месячные показатели.

2630 плюс 2245 плюс 2585, что составляет 7460. Вот мера и весь столбец.

Total Revenue = SUM ( Orders[Revenue] )

Revenue YTD = TOTALYTD ( [Total Revenue], 'Date'[Date] )

Апрель — это строка, на которую стоит обратить внимание. Выручка пуста, потому что нет заказов, а промежуточная сумма держится на уровне 7460, а не падает или исчезает, потому что с начала года означает все с 1 января до конца апреля, а это все еще 7460. В отчете без таблицы дат вообще не отображаются строки за апрель, и читатель никогда не узнает, что месяц прошел без каких-либо данных.

Эквивалент, написанный от руки, стоит увидеть один раз, потому что он показывает, что в функции нет никакой магии.

Revenue YTD Long =
CALCULATE (
    [Total Revenue],
    DATESYTD ( 'Date'[Date] )
)

Каждый раз интеллектуальная функция имеет такую ​​форму: РАСЧЕТ, фильтр которого представляет собой таблицу дат, созданную функцией даты. Поэтому правила РАСЧЕТА применяются здесь без изменений, и почему фильтр в таблице дат заменяет любой фильтр даты, который был у визуального элемента.

4. Предыдущий период с DATEADD и выдаваемая им ошибка

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

При правильной таблице дат она правильно возвращает пустое значение, поскольку апрель существует и в нем ничего нет. Без таблицы дат он может вернуть число марта, помеченное как число апреля, или выдать ошибку, и какая из них вы получите, зависит от формы дат в контексте.

Revenue Prior Month =
CALCULATE (
    [Total Revenue],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

Внимательно прочитайте последние две строки, потому что эта пара представляет собой все значение таблицы дат. Предыдущим месяцем апреля является март, 2585, поэтому в отчете показан месяц, в котором показатель с 2585 упал до нуля. Предыдущим месяцем мая является апрель, который пуст, поэтому в отчете корректно не учитывается изменение. Ни одно из этих предложений недоступно для отчета, пропущенного за апрель.

Два задокументированных факта о DATEADD стоит запомнить, поскольку они объясняют большинство ошибок, с которыми люди сталкиваются при его использовании.

Он возвращает только существующие даты. В ссылке Майкрософт говорится: «Таблица результатов включает только даты, которые существуют в столбце дат». Перейдите на дату, которой нет в вашем календаре, и вы ничего не получите взамен, молча.

Даты в контексте должны быть последовательными. На той же странице указано: «Если используется синтаксис столбца даты и даты в текущем контексте не образуют непрерывный интервал, функция возвращает ошибку». Это конкретная причина, по которой DATEADD не работает в модели без таблицы дат: значения Дата заказа в контексте — это фактические даты заказа, в которых есть пятинедельный пробел, а дыра не является непрерывным интервалом.

Для общего случая существует более короткая функция. PREVIOUSMONTH('Date'[Date]) делает ту же работу, что и DATEADD(..., -1, MONTH) нужно ввести меньше, а DATEADD следует оставить, поскольку он занимает любой интервал и любое число: минус 3 квартала, минус 1 год, плюс 2 недели.

5. Тот же период прошлого года и что означает пробел

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

Пусто в каждой строке, и это скорее правильный ответ, чем неудачный.

Revenue LY =
CALCULATE ( [Total Revenue], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Revenue YoY % =
DIVIDE ( [Total Revenue] - [Revenue LY], [Revenue LY] )

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

Об этом стоит сказать, потому что пустой столбец за предыдущий год — это самый распространенный отчет «показатель не работает», и примерно в половине случаев показатель верен, а истории просто нет. Два вопроса решают проблему за десять секунд. Таблица дат вообще охватывает прошлый год? А были ли какие-нибудь скандалы за прошлый год?

Если ваша таблица дат была построена с помощью CALENDAR(MIN(...), MAX(...)) по таблице фактов ответ на первый вопрос — нет, и в этом ошибка. Это практическая причина предпочесть CALENDARAUTO или явно расширенный диапазон: для показателя предыдущего года требуется предыдущий календарный год, даже если данные за предыдущий год отсутствуют.

Теперь представьте свою собственную модель. Если кто-то завтра спросит о росте в годовом исчислении, будет ли ваша таблица дат уже содержать год, предшествующий вашей самой ранней транзакции?

6. Месяц за месяцем, и месяц, которого нет

Перед сравнением: вот две версии одного и того же отчета: одна построена непосредственно на Дата заказа, а другая — на таблице дат. Посмотрите на майский ряд каждого из них и решите, какой из них говорит правду.

Revenue MoM % =
DIVIDE ( [Total Revenue] - [Revenue Prior Month], [Revenue Prior Month] )

Левая версия состоит из четырех строк, каждая из которых выглядит правдоподобной, но она неверна по двум различным причинам. Апреля нет, поэтому месяца, в котором бизнес ничего не продал, не видно. А майское значение -6,0 процента рассчитывается по сравнению с мартом, поскольку март находится строкой выше него, поэтому к двухмесячному сравнению прилагается метка «по сравнению с прошлым месяцем».

Правая версия имеет пять строк, сообщает об апреле как о полном коллапсе и отказывается вычислить изменение за май, поскольку месяцем ранее он был пуст. Этот пробел не является пробелом в отчете; это отчет, в котором говорится, что процентное изменение от нуля не имеет значения. Арифметика та же, что и в Месяц за месяцем в Excel, где один и тот же прибор дает одни и те же два ответа.

Ни в одной из колонок не требовалась другая мера. Мера одинакова в обоих случаях. Единственная разница заключается в том, содержит ли модель таблицу каждого месяца.

7. Финансовые годы и единственный аргумент, который меняет все

До изменения: год вашей организации заканчивается 30 июня. Прежде чем читать дальше, предскажите, что произойдет со столбцом за текущий год.

Вообще ничего, по этим данным, и понимание почему имеет смысл. TOTALYTD принимает необязательный последний аргумент для конца года.

Revenue FYTD =
TOTALYTD ( [Total Revenue], 'Date'[Date], , "6/30" )

В ссылке Майкрософт точна информация о формате: это «буквальная строка с датой, определяющей дату окончания года. По умолчанию — 31 декабря», часть года игнорируется, а рекомендуемый способ записи — месяц/день. Обратите внимание на пустой третий аргумент; этот слот является дополнительным фильтром, и его нужно оставить открытым с помощью запятой.

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

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

Крайние случаи, которые застают людей врасплох

Шесть, каждая из которых стоила кому-то дня.

Анализ времени по столбцу даты таблицы фактов. Иногда это срабатывает, что хуже, чем никогда не работать, потому что дает сбой только тогда, когда период оказывается пустым. Всегда передавайте столбец таблицы дат.

Столбец даты со временем в нем. Если Дата заказа содержит компонент времени, связь с календарем, содержащим только дату, ничему не соответствует, и каждая мера возвращает пустое значение. Удалите время в Мощность запроса.

Ось месяца отсортирована в алфавитном порядке. Апрель, февраль, январь, март, май. Исправлено с помощью сортировки по столбцу, и оно невидимо, пока кто-нибудь не прочитает диаграмму.

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

Два столбца дат, одна связь. Дата заказа и дата отгрузки не могут быть одновременно активно связаны с одним календарем. Либо используйте USERELATIONSHIP в конкретных показателях или создайте вторую таблицу дат (это подход, который Майкрософт рекомендует для ролевых измерений).

Автоматическая дата/время остаются включенными. Он создает скрытый календарь для каждого столбца даты, расширяет модель и дает вам группировку по месяцам, которая не распространяется на другие таблицы. Отключите его, как только появится реальная таблица дат.