Две даты в Power Pivot: продажи по заказу и по отгрузке через USERELATIONSHIP

Заказ оформили 31 января, а отгрузили 2 февраля. Коммерческий отчёт должен показать его в январе, складской — в феврале. В модели Power Pivot уже есть календарь, связанный с датой заказа. Если просто поместить месяц календаря в сводную и переименовать показатель в «Отгрузки», сумма останется январской. Нужно явно выбрать, по какой связи календарь фильтрует продажи для конкретной меры.

Подготовить две даты и один календарь

В таблице Sales создайте OrderDate, ShipDate и Amount. Одна строка должна соответствовать согласованной единице: для примера это заказ с одной полной отгрузкой. Если заказ отгружается частями в разные даты, потребуется таблица отгрузок с суммами частей, иначе одна ShipDate не сможет описать распределение выручки. Это вопрос данных, который нельзя исправить выбором формулы.

Таблица Calendar содержит по одной строке на каждую дату нужного диапазона, без дублей и пропусков. В представлении диаграммы Power Pivot создайте активную связь Calendar[Date] с Sales[OrderDate] и вторую, неактивную, с Sales[ShipDate]. Оба поля продаж должны содержать даты без времени, иначе полночь календаря не совпадёт с датой и временем отгрузки. Сторона календаря должна иметь уникальные ключи.

Сделать роль даты частью меры

В области вычислений таблицы Sales создайте две меры. Первая использует обычную активную связь. Вторая через CALCULATE включает уже существующую связь отгрузки на время расчёта. USERELATIONSHIP не создаёт связь по именам полей: сначала она должна быть определена в модели. В этом расчёте альтернативная связь заменяет активную между этими таблицами. После завершения меры устройство модели остаётся прежним.

Order Revenue := SUM(Sales[Amount])

Ship Revenue :=
CALCULATE(
    [Order Revenue],
    USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date])
)

Проверить январь и февраль на трёх заказах

Возьмём заказ A на 100 рублей от 31 января с отгрузкой 2 февраля. Заказ B на 200 оформлен и отгружен в январе. Заказ C на 300 оформлен 5 февраля, но ещё не отгружен, ShipDate пустая. Заказы января дают 300, февраля — 300. Отгрузки января дают 200, февраля — 100. Пустую отгрузку нельзя заменять датой заказа: это превратит обещанную поставку в состоявшуюся.

Создайте сводную по модели данных, поместите Calendar[YearMonth] в строки и обе меры в значения. Не берите месяц из Sales[OrderDate] для такого сравнения: этот столбец продолжит напрямую ограничивать заказы даже при расчёте отгрузок. Для общего календарного отчёта ось должна идти из Calendar. Если нужен независимый одновременный отбор по месяцу заказа и месяцу отгрузки, удобнее две ролевые таблицы календаря с разными именами.

Почему общий итог требует отдельного внимания

Без фильтра календаря мера суммы отгрузок может включить и ещё не отгруженный заказ, поскольку сам выбор связи не добавляет условие непустой ShipDate. Если смысл показателя требует только состоявшиеся отгрузки при любом контексте, добавьте к CALCULATE отдельный фильтр NOT ISBLANK(Sales[ShipDate]). Тогда итог по отгрузкам будет 300, а не 600. Называть такую меру стоит «Выручка отгруженных заказов», чтобы правило было видно пользователю.

Shipped Only Revenue :=
CALCULATE(
    [Order Revenue],
    USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date]),
    NOT ISBLANK(Sales[ShipDate])
)

Используйте Excel для Windows с доступным Power Pivot и моделью данных; наличие надстройки зависит от редакции Excel. В некоторых региональных настройках редактор использует точку с запятой вместо запятых. После ввода мер проверьте три учебных заказа, затем сверку по датам реальной отгрузки. Такое небольшое испытание сразу показывает, работает ли нужная роль календаря и не попадают ли в складской результат открытые заказы.

Продолжить работу с данными

В курсе «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов» вы разберёте факты и справочники, связи один-ко-многим, календарь и изменение фильтров через CALCULATE. Эти материалы помогут построить модель, где две меры по датам дают понятный результат и в строках, и в общем итоге. Откройте программу платного курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».