Работа с двумя полями дат в таблице

Вопрос или проблема

Я работаю с датами.
Мои поля в таблице заказов:

Дата начала
Дата окончания
Номер заказа.

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

Если дата начала в этом месяце и до этого месяца, а дата окончания в этом месяце, это означает, что заказ закрыт в этом месяце, он должен считаться 0.
Если дата начала в этом месяце или до этого месяца и закрыта в месяце после этого месяца, она должна считаться 1.

Сейчас я создал меру, такую как:
если(month(startdate) <= month(date), если(month(closedate) > month(date), 1, 0), 0)

Она присваивает 1 для условия, и затем я использую сумму всех единиц на визуале для каждого месяца. Сейчас это работает нормально с текущей датой начала, но для заказов с датой начала до этого месяца данные не отображаются, так как дата начала связана с полем даты в календарной таблице, когда я помещаю это в визуал. Она не показывает дату начала за предыдущие месяцы.
Например: если заказ имеет дату начала в 7-м месяце и дату окончания в 9-м месяце, он должен быть в 7-м месяце, а также в 8-м месяце.
Сейчас он показывает только в 7-м месяце, вероятно, из-за связи.
Кроме того, она не учитывает изменения года, например, она не может сравнивать данные за декабрь ’19 и январь ’20.

Я использовал этот пост, чтобы узнать, как смоделировать что-то похожее в PowerBI/DAX. В нем подводится итог, как использовать generate для создания расчетной таблицы, которая имеет одну строку на дату и заказ, показывающую статус. Когда пользователи создают связи с другими таблицами (датами и статусами в этом примере), тогда получить информацию о количестве открытых заказов или другой информации, которую вы включили в расчетную таблицу (я использовал цену контракта в своей настройке), довольно просто.

Ответ или решение

Когда речь идет о работе с датами в Power BI, правильная настройка отношений между таблицами и использование DAX для создания мер — ключ к успешному анализу данных. В вашем случае вы сталкиваетесь с задачей, связанной с отслеживанием открытых заказов по месяцам, что требует точного понимания структуры данных и логики выбора.

Проблема с текущими мерами

Ваше текущее выражение DAX:

if(month(startdate) <= month(date),
  if(month(closedate) > month(date), 1, 0), 0)

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

Оптимизация выражения DAX

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

1. Заказ считается открытым, если он начинается до или в данном месяце и закрывается после него (или не закрыт вовсе).
2. Заказы, которые закрываются в текущем месяце, останутся с нулевым значением.

Вот обновленная мера:

OpenOrders =
VAR StartYear = YEAR(Min(OrderTable[StartDate]))
VAR StartMonth = MONTH(Min(OrderTable[StartDate]))
VAR CloseYear = YEAR(Min(OrderTable[CloseDate]))
VAR CloseMonth = MONTH(Min(OrderTable[CloseDate]))
VAR CurrentYear = YEAR(MAX(DateTable[Date]))
VAR CurrentMonth = MONTH(MAX(DateTable[Date]))

RETURN
IF(
    (StartYear < CurrentYear || (StartYear = CurrentYear && StartMonth <= CurrentMonth)) &&
    (CloseYear > CurrentYear || (CloseYear = CurrentYear && CloseMonth > CurrentMonth)),
    1,
    0
)

Объяснение логики

1. Проверка дат: Мы вычисляем год и месяц для StartDate и CloseDate, а также для текущей даты в контексте визуала.
2. Условие: Используем условие, чтобы проверить, находится ли дата начала до или в текущем месяце, и возвращает ли дата закрытия значение после текущего месяца.
3. Возврат результата: Если оба условия выполняются — возвращаем 1, иначе 0.

Настройка отношений

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

• Связывайте таблицу дат по StartDate, чтобы у вас были доступные даты для выражения DAX.
• Не забывайте, что при наличии активации фильтров в Power BI некоторым визуалам могут не отображаться данные, поэтому тестируйте на малом наборе данных.

Подводя итог

Работа с датами в Power BI требует тщательного анализа и понимания форматов данных. С помощью обновленного выражения DAX и правильной настройки отношений, вы сможете адекватно отслеживать открытые заказы по месяцам, независимо от их дат начала и окончания. Убедитесь, что вы протестировали алгоритм на различных сценариях, чтобы гарантировать его правильность.

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