Правила DATEADD в DAX / Хабр

Привет, Хабр! Важной составной частью Time Intelligence в DAX являются функции работы со временем, в частности, DATEADD, т.к. она является базовой для других (например, SAMEPERIODLASTYEAR является псевдонимом DATEADD('Date'[Date], -1, YEAR)) и возвращает таблицу (в отличие, например, от EDATE , которая возвращает только скаляр), так и использоваться в качестве фильтра в CALCULATE.

Информацию о DATEADD приходится собирать из разных источников. Часть описано в официальной документации DATEADD, что-то есть в DAX Guide, что-то есть в материалах SQL BI, поэтому картина составляется по частям, хотя логика функции неочевидна и велики риски ошибок при использовании DATEADD в случае некорректного её использования.

Интересующимся правилами DATEADD для обеспечения Time Intelligence в DAX — добро пожаловать под кат :)

Важной задачей при смещении периодов времени (месяцы, годы) является необходимость учесть разное количество дней в разных месяцах и годах (для смещений на годы — с учетом високосного года). Можно сказать, что у функции DATEADD нет таких проблем, она возвращает целый месяц при смещении, если в исходном диапазоне был выбран ровно месяц. Для реализации такого поведения функция DATEADD следует трем следующим правилам.

1. Функция DATEADD возвращает только даты, существующие в столбце с датами. Если после смещения даты в этой таблице не найдены, функция вернет только те даты, которые есть в таблице.

2. Если дата отсутствует в соответствующем месяце после операц��и смещения, то функция DATEADD вернет последний день соответствующего месяца.

3. Если текущие даты для сдвига включают в себя два последних дня месяца, то в результат функции DATEADD войдут все даты между соответствующими днями в смещенной таблице и окончанием месяца. Как следствие, если выбрана одна дата, то Time Intelligence логика с периодами не применяется к одной дате, поскольку единственная дата — это не период.

Рассмотрим правила детальнее с DAX примерами и примерами из ClickHouse. Примеры ClickHouse нужны лишь для демонстрации базовой возможной реализации.

Правило 1 — только даты из календаря

DAX при сдвиге периодов времени не генерирует даты — он берёт их только из таблицы дат date.

В связи с этим следующее поведение не соответствует DAX:

SELECT addMonths(date, 1)
FROM selected_dates

Приведенный выше пример создаёт даты, даже если их нет в календаре, что не соответствует DAX. DAX-эквивалент в ClickHouse может быть следующим:

SELECT c.date
FROM selected_dates s
INNER JOIN calendar c
    ON c.date = addMonths(s.date, 1)

В этом случае если даты нет в календаре, то она не попадёт в результат, что совпадает с DAX.

Правило 2 — если дня нет, то берется последний день месяца

Пример DAX:

31 января + 1 месяц = 28 февраля

В ClickHouse это корректно имплементировано через addMonths , то есть правило 2 уже соблюдается автоматически:

SELECT addMonths(toDate('2024-01-31'), 1)
-- 2024-02-29

Правило 3 — если выбраны два последних дня месяца, то вернуть период до конца месяца

Это самое нетривиальное правило DAX и СУБД зачастую не имеют аналогичного функционала. В частности, в ClickHouse этого нет, поэтому для демонстрации соответствующей логики SQL был реализован, который в общих чертах показыавет особенности логики Time Intelligence.

Если выбраны два последних дня месяца, то включается Time Intelligence логика, обеспечивающая корректное количество дней во всех месяцах:

Remember that when it comes to the DAX time intelligence functions, saying that an entire month is selected means that all the values in the Date table for that month are visible in the filter context. This algorithm is required to return the 31 days in January in case February (which has only 28 or 29 days) is selected.

Пусть выбраны даты:

29 апреля
30 апреля

DATEADD со смещением на один месяц назад возвращет:

29 марта
30 марта
31 марта - день добавился автоматически

Рассмотрим схематичное решение-прототип на ClickHouse с ограничениями (диапазон дат для смещения непрерывный и в рамках одного месяца) — лишь для иллюстрации объема логики SQL, которая нужна для имплементации DATEADD из DAX.

Шаг1 — проверить, что выбраны два последних дня месяца:

WITH last_two_check AS
(
    SELECT count() AS cnt
    FROM selected_dates
    WHERE date >= addDays(toLastDayOfMonth(date), -1)
)
SELECT cnt FROM last_two_check

Шаг 2 — границы выбранного диапазона:

WITH bounds AS
(
    SELECT
        min(date) AS min_d,
        max(date) AS max_d
    FROM selected_dates
)

Шаг 3 — смещённый диапазон:

WITH bounds AS
(
    SELECT
        min(date) AS min_d,
        max(date) AS max_d
    FROM selected_dates
)

SELECT
    addMonths(min_d, 1) AS new_start,
    toLastDayOfMonth(addMonths(max_d, 1)) AS new_end
FROM bounds

Полный DAX-эквивалент DATEADD в ClickHouse с описанными ограничениями:

-- Ограничения:
-- начало и конец выбранного периода в одном месяце
-- нет промежутков в таблице дат

WITH
-- смещение в месяцах
    -1 AS shift,

    selected AS
        (
            SELECT arrayJoin([
                toDate('2024-04-29'),
                toDate('2024-04-30')
                ]) AS date
        ),

-- проверка на два последний дня месяца
    last_two_check AS
        (
            SELECT count() AS cnt
            FROM selected
            WHERE date >= addDays(toLastDayOfMonth(date), -1)
        ),

    bounds AS
        (
            SELECT
                min(date) AS min_d,
                max(date) AS max_d
            FROM selected
        ),

-- смещенный период дат
    range_shift AS
        (
            SELECT
                addMonths(min_d, shift) AS new_start,
                toLastDayOfMonth(addMonths(max_d, shift)) AS new_end
            FROM bounds
        ),

    calendar AS
        (
            SELECT toDate('2000-01-01') + number AS date
            FROM numbers(36500)
        )

-- режим DAX "расширить до конца месяца"
SELECT c.date
FROM calendar c
         CROSS JOIN range_shift r
         CROSS JOIN last_two_check l
WHERE c.date BETWEEN r.new_start AND r.new_end
  AND l.cnt = 2

UNION ALL

-- обычный режим DAX
SELECT addMonths(date, shift)
FROM selected
         CROSS JOIN last_two_check l
WHERE l.cnt != 2

ORDER BY date;

Результат выполнения запроса:

Как было описано, в этой имплементации есть ограничения — непрерывный период в рамках одного месяца и непрерывная таблица дат date.

Важно отметить, что для корректного количества дней в месяце и для корректного применения DATEADD важно использовать функцию как фильтр в CALCULATE, поскольку другие подходы могут отключить логику Time Intelligence и приведут к некорретному количеству дней в месяцах.

Так, при использовании CALCULATE и DATEADD — все корректно, но при использовании конструкций с CALCULATE и FILTER происхоит «выкалывание дат» и логика Time Intelligence отключается, что видно на примере этого DAX:

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year Month],
    "DATEADD в FILTER",
        CALCULATE (
            DISTINCTCOUNT ( 'Date'[Date] ),
            FILTER (
                ALL ( 'Date' ),
                'Date'[Date] >= MINX ( 'Date', DATEADD ( 'Date'[Date], -1, MONTH ) )
                    && 'Date'[Date] <= MAXX ( 'Date', DATEADD ( 'Date'[Date], -1, MONTH ) )
            )
        ),
    "DATEADD как фильтр CALCULATE",
        CALCULATE (
            DISTINCTCOUNT ( 'Date'[Date] ),
            DATEADD ( 'Date'[Date], -1, MONTH )
        ),
    "Обычная сумма за текущий месяц", DISTINCTCOUNT ( 'Date'[Date] )
)

В результаты выполнения запроса видно, что DATEADD в FILTER "выкалывает" даты и берет минимальное количество дней в текущем и предыдущем месяце, а DATEADD в CALCULATE реализует ожидаемую TIme Intelligence логику с актуальным количеством дней в каждом месяце.

Примеры DAX для DATEADD на модели Contoso

Рассмотрим примеры на Contoso из dax.do. Во всех примерах используется таблица 'Date' из Contoso.

Правило 1

DATEADD возвращает только даты, существующие в таблице дат

Проверим, что DATEADD не создаёт новые даты, а работает только с теми, что есть в календаре.

Пример 1 — демонстрация возврата только существующих дат

Выберем один месяц и посмотрим, какие даты вернёт DATEADD.

EVALUATE
VAR CurrentMonth =
    FILTER ( 'Date', 'Date'[Calendar Year] = "CY 2008" && 'Date'[Month Number] = 3 )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            CurrentMonth,
            "Shifted Date", CALCULATE ( MAX ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
        ),
        'Date'[Date],
        [Shifted Date]
    )
ORDER BY 'Date'[Date]

Видно, что каждая дата марта сопоставляется только с реально существующей датой февраля.

Пример 2 — проверка количества дней

EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year Month],
    "Days Current",
        DISTINCTCOUNT ( 'Date'[Date] ),
    "Days Previous Month",
        CALCULATE (
            DISTINCTCOUNT ( 'Date'[Date] ),
            DATEADD ( 'Date'[Date], -1, MONTH )
        )
)
ORDER BY 'Date'[Calendar Year Month]

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

Правило 2

Если дата отсутствует в целевом месяце — возвращается последний день месяца

Contoso отлично показывает этот эффект на переходах между месяцами с разной длиной.

Пример 3 — переход 31-го дня месяца в дату конца месяца

EVALUATE
SELECTCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            FILTER ( 'Date', DAY ( 'Date'[Date] ) >= 28 ),
            "Previous Month Date", CALCULATE ( MAX ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
        ),
        MONTH ( 'Date'[Date] ) = 3 && 'Date'[Calendar Year] = "CY 2008"
    ),
    'Date'[Date],
    [Previous Month Date]
)
ORDER BY 'Date'[Date]

Получаем результат (2008 — високосный год):

Пример 4 — сравнение с ручным смещением

EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        FILTER ( 'Date', MONTH ( 'Date'[Date] ) = 3 && DAY ( 'Date'[Date] ) >= 29 ),
        "Manual Shift", EDATE ( 'Date'[Date], -1 ),
        "DATEADD Shift", CALCULATE ( MAX ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
    ),
    'Date'[Date],
    [Manual Shift],
    [DATEADD Shift]
)

Можно показать, что DATEADD повторяет поведение EDATE, т.к. всего одна дата

Правило 3

Если выбраны два последних дня месяца — DATEADD расширяет диапазон до конца месяца

Это самое интересное правило, которое может вызывать трудности у аналитиков и разработчиков.

Пример 5 — сравнение 1 последнего дня vs 2 последних дней

EVALUATE
VAR OneDay =
    FILTER (
        'Date',
        'Date'[Date] = DATE ( 2008, 3, 31 )
    )

VAR TwoDays =
    FILTER (
        'Date',
        'Date'[Date] IN { DATE ( 2008, 3, 30 ), DATE ( 2008, 3, 31 ) }
    )

RETURN
UNION (
    SELECTCOLUMNS (
        OneDay,
        "Date", 'Date'[Date],
        "Scenario", "One Day",
        "Previous Month Date",
            CALCULATE (
                SELECTEDVALUE ( 'Date'[Date] ),
                DATEADD ( 'Date'[Date], -1, MONTH )
            )
    ),
    SELECTCOLUMNS (
        TwoDays,
        "Date", 'Date'[Date],
        "Scenario", "Two Days",
        "Previous Month Date",
            CALCULATE (
                SELECTEDVALUE ( 'Date'[Date] ),
                DATEADD ( 'Date'[Date], -1, MONTH )
            )
    )
)
ORDER BY [Scenario], [Date]

Этот пример иллюстрирует включение Time Intelligence логики.

Демонстрация: DATEADD всегда возвращает таблицу

Пример 6

EVALUATE
{ COUNTROWS ( DATEADD ( VALUES ( 'Date'[Date] ), -1, MONTH ) ) }

Этот пример хорошо объясняет, почему нельзя сравнивать DATEADD с одиночным значением.

Контрастный пример — некорректное использование

Пример 7 — пустой результат

EVALUATE
CALCULATETABLE (
    VALUES ( 'Date'[Date] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] = DATEADD ( 'Date'[Date], -1, MONTH )
    )
)

Этот пример хорошо показывает классическую ошибку, этот DAX запрос не возвращает ни одной строки.

Edge-кейсы DATEADD в DAX

Ниже приведены запросы на примере схемы Contoso в dax.do, в которых поведение DATEADD может быть интуитивно непонятным.

Edge-кейс №1

Выбор одной даты — Time Intelligence не включается

Это фундаментальное, но редко явно проговариваемое правило.

EVALUATE
VAR SingleDate =
    FILTER (
        'Date',
        'Date'[Date] = DATE ( 2008, 3, 31 )
    )

RETURN
CALCULATETABLE (
    VALUES ( 'Date'[Date] ),
    DATEADD (
        CALCULATETABLE ( VALUES ( 'Date'[Date] ), SingleDate ),
        -1,
        MONTH
    )
)

Возвращаемый результат:

Если выбрана одна дата, DATEADD ведёт себя как обычное смещение даты и расширение диапазона не применяется. Если добавить ещё одну дату — логика уже меняется.

Edge-кейс №2

Две даты включают логику периодов

EVALUATE
VAR TwoDates =
    FILTER (
        'Date',
        'Date'[Date] IN {
            DATE ( 2008, 3, 30 ),
            DATE ( 2008, 3, 31 )
        }
    )

RETURN
SELECTCOLUMNS (
    TwoDates,
    "Date", 'Date'[Date],
    "Previous Month Date",
        CALCULATE (
            SELECTEDVALUE ( 'Date'[Date] ),
            DATEADD ( 'Date'[Date], -1, MONTH )
        )
)
ORDER BY [Date]

В результате получаем:

Разработчик может интуитивно ожидать увидеть две даты:

28.02
29.02

Но DAX воспринимает выбор как конец месяца и возвращает хвост месяца.

Edge-кейс №3

Пропуски в календаре ломают Time Intelligence

Рассмотрим выбранный диапазон дат с выколотыми датами.

EVALUATE
VAR CustomCalendar =
    FILTER (
        'Date',
        NOT ( DAY ( 'Date'[Date] ) IN { 10, 11, 12 } )
            && 'Date'[Calendar Year] = "CY 2008"
            && 'Date'[Month Number] = 3
    )

RETURN
ROW (
    "Rows",
    CALCULATE (
        COUNTROWS ( 'Date' ),
        CustomCalendar,
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
)

В результате запроса количество дат становится меньше ожидаемого, с учетом того, что в високосном 2008 году в февраля 29 дней.

Если календарная таблица не содержит все даты — DATEADD начинает вести себя нестабильно. Это может быть частым источним багов.

Edge-кейс №4

Високосные годы

Рассмотрим пример для февраля високосного 2008 года:

EVALUATE
SELECTCOLUMNS (
    FILTER (
        ADDCOLUMNS (
            FILTER ( 'Date', MONTH ( 'Date'[Date] ) = 3 ),
            "Previous Month", CALCULATE ( MAX ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
        ),
        DAY ( 'Date'[Date] ) >= 28 && 'Date'[Calendar Year] = "CY 2008"
    ),
    'Date'[Date],
    [Previous Month]
)
ORDER BY 'Date'[Date]

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

30 марта → 29 февраля
31 марта → 29 февраля

Edge-кейс №5

Переход через границу года

Пример

EVALUATE
VAR January =
    FILTER (
        'Date',
        'Date'[Calendar Year] = "CY 2008"
            && 'Date'[Month Number] = 1
    )

RETURN
SUMMARIZECOLUMNS (
    January,
    "Previous Period",
        CALCULATE (
            MIN ( 'Date'[Date] ),
            DATEADD ( 'Date'[Date], -1, MONTH )
        )
)

DATEADD корректно переходит в декабрь предыдущего года:

Но если календарь обрезан — вернётся пустой результат.

Edge-кейс №6

Несмежные диапазоны дат

Рассмотрим поведение для несмежного диапазона дат:

EVALUATE
VAR RandomDays =
    FILTER (
        'Date',
        'Date'[Date] IN {
            DATE ( 2008, 3, 5 ),
            DATE ( 2008, 3, 25 )
        }
    )

RETURN
CALCULATETABLE (
    VALUES ( 'Date'[Date] ),
    RandomDays,
    DATEADD ( 'Date'[Date], -1, MONTH )
)
ORDER BY 'Date'[Date]

Будут возвращены две отдельные даты:

DATEADD не пытается расширять несмежные диапазоны.

Edge-кейс №7

Использование DATEADD вне CALCULATE

Рассмотрим использование DATEADD в EVALUATE:

EVALUATE
DATEADD ( VALUES ( 'Date'[Date] ), -1, MONTH )

DATEADD возвращает таблицу, DATEADD становится фильтром только внутри CALCULATE:

Edge-кейс №8

DATEADD vs EDATE

Сравним поведение DATEADD и EDATE:

EVALUATE
SELECTCOLUMNS (
    ADDCOLUMNS (
        FILTER ( 'Date', DAY ( 'Date'[Date] ) = 31 ),
        "EDATE", EDATE ( 'Date'[Date], -1 ),
        "DATEADD", CALCULATE ( MAX ( 'Date'[Date] ), DATEADD ( 'Date'[Date], -1, MONTH ) )
    ),
    [EDATE],
    [DATEADD]
)
ORDER BY [EDATE]

Вижно, что DATEADD работает в рамках таблицы-календаря:

Edge-кейс №9

Последний день месяца и фильтр по рабочим дням

Использование фильтра может отключить логику Time Intelligence по периодам:

EVALUATE
VAR Mondays =
    FILTER (
        'Date',
        'Date'[Day of Week] = "Monday"
            && 'Date'[Calendar Year] = "CY 2008"
            && 'Date'[Month Number] = 3
    )

RETURN
ROW (
    "Result",
    CALCULATE (
        COUNTROWS ( 'Date' ),
        Mondays,
        DATEADD ( 'Date'[Date], -1, MONTH )
    )
)

Из-за фильтра DATEADD перестаёт воспринимать период как месяц:

Edge-кейс №10

Потеря дат из-за контекста DATEADD

Если в контексте группировки нет даты 'Date'[Date], то это может привести к некорректной работе DATEADD и «выколотым» датам:

EVALUATE
ADDCOLUMNS (
    FILTER (
        'Date',
        'Date'[Date] >= DATE ( 2008, 1, 25 )
            && 'Date'[Date] <= DATE ( 2008, 3, 5 )
    ),
    "Prev Month Date",
        CALCULATE (
            MAX ( 'Date'[Date] ),
            DATEADD ( 'Date'[Date], -1, MONTH )
        )
)

В результате в этом запросе месяц январь неполный:

Выводы

Если кратко резюмировать, то помимо трех перечисленных правил, поведение DATEADD также зависит от:

непрерывности диапазона
количества выбранных дат
положения внутри месяца
полноты календаря
контекста CALCULATE

Надеюсь, приведенные примеры могут быть полезны, успешных дашдордов! :)