Есть класс задач, где у события два момента: начало и конец. Заказ оформлен и отгружен; договор открыт и закрыт; сотрудник нанят и уволен. Вопрос — «сколько событий активно на каждую дату» — кажется простым, но обычным COUNTROWS не решается. Это паттерн events in progress. Разбираем по DAX Patterns SQLBI.
Учебный набор — завершённые продажи. Поэтому пример на гипотетической таблице Заказы со столбцами ДатаОформления и ДатаОтгрузки. Логика та же для подписок, договоров, тикетов.
Почему это нетривиально
Каждый заказ — одна строка с двумя датами. А вопрос — про каждый день между ними: на 5 марта заказ ещё открыт, на 10-е уже отгружен. То есть одна строка факта должна «отметиться» на множестве дат. Прямой связи «заказ → дата» для этого мало.
Идея: дата между началом и концом
Событие активно на дату D, если оно началось не позже D и ещё не закончилось на D:
ДатаОформления <= D И ( ДатаОтгрузки > D ИЛИ ДатаОтгрузки пуста )Считаем такие строки для каждой даты из календаря. Календарь здесь — «движок»: он перебирает даты, а мера на каждой считает, сколько заказов попадает в интервал.
Мера
Открытые заказы =
VAR _D = MAX ( Календарь[Дата] )
RETURN
CALCULATE (
COUNTROWS ( Заказы ),
FILTER (
ALL ( Заказы ),
Заказы[ДатаОформления] <= _D
&& ( Заказы[ДатаОтгрузки] > _D || ISBLANK ( Заказы[ДатаОтгрузки] ) )
)
)MAX(Календарь[Дата]) — текущая дата в контексте (последний день периода/строки). FILTER по ALL(Заказы) отбирает заказы, активные на эту дату. Положите меру на ось дат — получите кривую «сколько открыто в каждый день».
Календарь не связан с обеими датами заказа физически — он служит «таймлайном», а мера через FILTER сама проверяет попадание интервала [начало; конец) на текущую дату. Так одна строка факта «раскрывается» на все свои дни.
Тонкости
- Граница интервала. Решите, включать ли день отгрузки:
>или>=. Для «открыт на конец дня» обычноДатаОтгрузки > D. - Незавершённые. Пустая
ДатаОтгрузки= ещё открыт; не забудьтеISBLANK. - Производительность.
FILTER(ALL(Заказы))на каждую дату — дорого на больших фактах. На проде оптимизируют: разворачивают заказы в «снимки по датам» в ETL, либо считают накопительно (открыто = оформлено − отгружено нарастающим итогом).
Накопительный приём (быстрее)
Эквивалент без перебора интервалов: открыто на дату = (оформлено всего до даты) − (отгружено всего до даты).
Открытые заказы (накопит.) =
VAR _D = MAX ( Календарь[Дата] )
VAR _Оформлено = CALCULATE ( COUNTROWS ( Заказы ), Заказы[ДатаОформления] <= _D )
VAR _Отгружено = CALCULATE ( COUNTROWS ( Заказы ), Заказы[ДатаОтгрузки] <= _D )
RETURN
_Оформлено - _ОтгруженоЧасто это быстрее FILTER по интервалу и проще читается.
COUNTROWS(Заказы) в контексте даты даст заказы, у которых эта дата = дата оформления, а не все активные. «Открыто на дату» — это интервальная логика, а не принадлежность одной дате.
Что дальше
Интервальные события закрыли. Последний паттерн блока — конвертация валют. Следующий урок.