DAX
December 19, 2022

План - отобрать и поделить!

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

В качестве примера возьмём простую модель, где будет только три таблицы: календарь, план и факт.

На этот раз я использую производственный календарь, который я загрузил с сайта Консультант и который учитывает не только выходные дни, но и праздничные, а также переносы выходных с ними связанные (спасибо Алексей (PooHkrd) Семилетов за создание календаря, а о том как создать календарь с помощью DAX читайте в другой моей статье).

Производственный календарь

Таблица фактов Orders содержит информацию о продажах по дням:

Таблица фактов

А вот планы разбиты по месяцам (месяц указан в формате даты первого числа месяца):

Таблица планов

Так как и факты, и планы содержат столбец с датами, то свяжем их с календарем по дате:

Схема связей модели

Также нам понадобится несколько мер:

Базовые меры

Перейдём к визуализации и посмотрим на меры, сначала по годам:

План и факт по годам

Затем по месяцам:

План и факт в разбивке Год-месяц
Столбец StartMonth в календаре отформатирован с помощью пользовательского формата, подробнее как это сделать можно прочитать здесь.

А теперь развернём один месяц до дней:

План и факт по дням

Факты бьются по дням, а вот месячный план весь записан на первое число месяца, так как таблица Plans связана с календарём по столбцу дат, а в самой таблице планов месяц указан в виде "01.ММ.ГГГГ".

Как показать значение плана на каждый день?

Одно из решений - это создать промежуточную таблицу уникальных значений "Год-месяц" или "StartMonth" и связать таблицу планов с календарём через неё.

Так как у меня планы БЕЗ какой-либо разбивки, кроме месяцев, то мне такая таблица не нужна и я просто могу связать столбцы Plans[Month Plan] и Calendar[StartMonth]:

Связь календаря и планов по месяцу

А так как поле Calendar[StartMonth] содержит повторяющиеся значения, то необходимо будет установить двунаправленную связь, чтобы можно было передавать фильтр из календаря и в факты, и в планы:

Месячный план на каждый день

Теперь план есть на каждую дату, но всё ещё является некорректным, так как это значение месячного плана, а не дневного. И здесь также есть несколько решений:

  • разделить на количество дней в месяце ДО загрузки в модель
  • разделить на количество дней в месяце в мере

Воспользуемся вторым способом, а за одно и откажемся от промежуточных таблиц, посчитав планы на каждую дату с помощью меры, при этом связь будет использоваться Calendar[Date]->Plans[Month Plan].

Месячный план на каждую дату с помощью меры

С помощью функции ALLEXCEPT оставим фильтр по таблице дат только по столбцу StartMonth.

Чтобы разделить месячный план по дням, необходимо получить число дней в месяце. Это можно сделать несколькими способами, например с помощью функции EOMONTH:

План деленный на количество дней в месяце

Но, как всегда, есть нюансы - в строке итогов теперь план на день. Это можно обработать с помощью функции ISINSCOPE:

Обработка итогов
Для особо внимательных читателей: если сложить значения дневного плана за месяц, то получим 101+101+...101=3131, а не 3125. Просто значения меры отформатированы до целого числа, на самом деле там 3125/31=100,80645.. Тоже самое касается и дальнейших расчётов - это просто заданный формат отображения в виде целого числа.

А можно добавить более универсальную обработку:

Еще один вариант расчета дневного плана

Теперь, если в фильтре будет выбран не полный месяц, то и в строке итогов будет считаться план на выбранное количество дней:

Некорректный план на неполный месяц

СТОП!!! Тут вообще какие-то непонятные цифры. Откуда они взялись? Это произошло из-за того, что я теперь использую в качестве фильтра не столбец StartMonth, а столбец Date. А функция ALLEXCEPT не может добавлять фильтры - она их только сохраняет. Подробнее об этом можно прочитать у Марко Руссо и Альберто Феррари.

Я же просто создам новую меру:

Корректный план на неполный месяц

Но, не зря же я использовал в этом файле производственный календарь. Определение количества дней в месяце по последнему дню подойдёт, если вы работаете каждый день. Но, если вы осуществляете продажи только в рабочие дни, при этом необходимо учитывать праздники и переносы, то шаблон меры дневного плана необходимо будет изменить:

Распределение плана только по рабочим дням

Всего в ноябре 2022 года был 21 рабочий день и 9 выходных в том числе 4-е число пятница - День народного единства. Как видно на скриншоте, при распределении плана на все дни месяца и только рабочие дни - кардинально меняется результат.

Целью этой статьи было показать различные способы решения одной и той же задачи. Некоторые способы лучше, некоторые хуже. Но в большей степени их эффективность будет зависеть от данных в вашей модели, их структуры, от визуализаций в отчёте и фильтров, которые вы используете. И, прежде всего, от вопросов, на которые вы хотите ответить.

Ссылка на скачивание файла с мерами.