Компания продаёт в рублях, долларах и юанях, а отчёт нужен в одной валюте. Соблазн — завести [Выручка USD], [Выручка EUR]… — не масштабируется. Правильно: одна мера + таблица курсов. Разбираем по DAX Patterns SQLBI.
В учебном наборе одна валюта. Поэтому пример на расширенной схеме: Продажи[Валюта, Сумма], справочник Курсы[Дата, Валюта, Курс] (курс к отчётной валюте), и Календарь.
Две модели курсов
Сначала решите, как часто меняется курс:
- Дневные курсы — курс на каждый день. Точнее, тяжелее. Конвертируем каждую продажу по курсу её дня.
- Месячные курсы — один курс на месяц (например, средний). Проще и часто достаточно для управленческой отчётности.
От этого зависит грануляция связи Курсы с Календарём.
Базовый паттерн — построчно
Главный приём: конвертируем на уровне строки факта, потом суммируем. Курс берём по дате и валюте каждой продажи:
Выручка в отчётной валюте =
SUMX (
Продажи,
VAR _Курс =
LOOKUPVALUE (
Курсы[Курс],
Курсы[Дата], Продажи[Дата],
Курсы[Валюта], Продажи[Валюта]
)
RETURN
Продажи[Сумма] * _Курс
)SUMX идёт по продажам, для каждой LOOKUPVALUE достаёт курс на её дату и валюту, умножает и складывает. Это семейство итераторов (урок про X-функции) в чистом виде.
Нельзя сконвертировать «итог» — у разных строк разные валюты и даты. Конвертация делается на самом детальном уровне (строка продажи), и только потом суммируется. Это ключевая идея валютного паттерна.
Через связь и USERELATIONSHIP
Если Курсы связаны с Календарём и со справочником валют, вместо LOOKUPVALUE используют связи и RELATED/USERELATIONSHIP (уроки про связи). Это быстрее на больших фактах, чем LOOKUPVALUE в каждой строке, но требует аккуратной модели курсов.
Пропущенные курсы
Курса ровно на дату продажи может не быть (выходной, праздник). Стандартное решение — брать последний доступный курс на дату ≤ даты продажи:
VAR _Курс =
CALCULATE (
LASTNONBLANKVALUE ( Курсы[Дата], MAX ( Курсы[Курс] ) ),
Курсы[Дата] <= Продажи[Дата],
Курсы[Валюта] = Продажи[Валюта]
)Это та же логика «последнего значения», что в semi-additive остатках.
[Выручка USD], [Выручка EUR], [Выручка CNY] — антипаттерн: не масштабируется, дублирует логику, ломается на новой валюте. Одна мера + таблица курсов + (опционально) выбор отчётной валюты параметром (урок про what-if) закрывают любое число валют.
Выбор отчётной валюты пользователем
Отчётную валюту можно сделать параметром: пользователь выбирает «в чём смотреть», а мера берёт курсы к этой валюте. Это disconnected-параметр + SELECTEDVALUE (урок про what-if), наложенный на паттерн выше.
Что дальше
Валютный паттерн — последний в блоке DAX-паттернов. Дальше — экспертный курс «Эксплуатация и масштаб»: RLS, инкрементальное обновление, оптимизация, модели на SSAS. Курс пополняется.