Время прочтения: 7 мин.

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

Для решения данной задачи необходим производственный календарь.

На просторах интернета есть идеи как можно спарсить производственный календарь, но источники данных не всегда официальные (что может отразиться на качестве отчета) + политика моей компании не позволяла использовать внешние источники данных для модели. Также в связи со случаями, введенными Правительством РФ, когда организации могут работать в нерабочие дни из-за производственной необходимостью, иметь персонализированный календарик – это хорошая затея. Поэтому я сделала свой производственный календарь для пятидневной рабочей недели на основе данных Консультанта (заняло это у меня минут 10 для данных 2018-2022 годов).

Файл с календарем размещен по ссылке.

Ниже описаны примеры калькуляторов в зависимости от имеющихся данных (Дата начала/окончания работы над задачи, нормативный срок).

Расчет кол-ва рабочих дней между датами

Пример: посчитать за сколько раб. дней была решена задача.

Дата начала 07.02.2022.

Дата окончания 15.02.2022.

Когда производственный календарь (Таблица ‘Calendar’ со столбцами [Дата] и бинарным признаком рабочих дней [рабдни]) загружен в модель данных и формат столбцов корректен, произведем расчет.

Для того, чтобы написать правильную формулу в DAX, попробуем составить логику вручную.

  1. Сделаем фильтрацию производственного календаря, чтобы в нем остались только необходимые даты [Дата начала; Дата окончания] и только рабочие дни (рабдни=1).

2. Посчитать сколько дней (строк в таблице) получилось – это и есть интересующая цифра. Количество строк/раб. дней=7.

Теперь напишем формулу расчета в DAX в таблице, где хранятся даты начала\окончания задач.

CALCULATE (COUNTROWS ('Calendar'), FILTER('Calendar', 'Calendar'[Дата].[Date]>=[Дата начала]&& 'Calendar'[Дата].[Date]<=[Дата окончания] &&'Calendar'[рабдни]=1)).

Расчет прогнозного срока завершения задачи

Например, у нас есть данные, что задача начата 07.02.2022 и по плану должна быть завершена в срок 7 р.д.

Аналогично предыдущему примеру, сделаем действия компьютера руками.

  1. Отфильтруем производственный календарь, чтобы даты были не позднее чем 07.02.2022 и в нем остались только рабочие дни (Раб. Дни=1).
  2. Столбец «Дата» отфильтруем по возрастанию и оставим только первые 7 строк (т.к. задача по плану должна быть завершена в срок 7 р.д).

3. Самая поздняя дата (15.02.2022) и будет решением нашего вопроса.

Теперь напишем формулу расчета в DAX в таблице, где хранятся даты начала\окончания задач.

CALCULATE (MAX('Calendar'[Дата].[Date]),TOPN (7, FILTER('Calendar', 'Calendar'[Дата].[Date]>=[Дата начала]&&'Calendar'[рабдни]=1),'Calendar'[Дата].[Date], ASC)).

Расчет даты начала работы над задачей

Например, у нас есть данные что сотрудник закончил задачу 07.02.2022 и работал над ней 7 р.д. Задача – вычислить дату, когда сотрудник начал работу над данной задачей.

  1. Отфильтруем производственный календарь, чтобы даты были не раньше чем 07.02.2022 и в нем остались только рабочие дни (Раб. Дни=1).

2. Столбец «Дата» отфильтруем по убыванию и оставим только первые 7 строк (т.к. задача завершена в срок 7 р.д).

3. Самая ранняя дата (28.01.2022) и будет решением нашего вопроса.

Теперь напишем формулу расчета в DAX в таблице, где хранятся даты начала\окончания задач.

CALCULATE(MIN ('Calendar'[Дата].[Date]), TOPN (7, FILTER('Calendar', 'Calendar'[Дата].[Date]>=[Дата окончания].[Date]&&'Calendar'[рабдни]=1),'Calendar'[Дата].[Date], DESC))

 Также прилагаю модель данных в Power BI. Файл «Калькулятор рабочих дней в DAX» размещен по ссылке.

Калькулятор рабочих дней в DAX (с учетом времени)

Рассмотрим случай, когда необходимо рассчитать рабочие дни между датой поступления и датой выполнения запроса. Обычный подсчет рабочих дней в DAX мы рассмотрели в статье (ссылка на пред. статью). А если необходимо рассчитать рабочие дни, с учетом времени поступления/выполнения. Например, запрос пришел в четверг в 11.54 и выполнен в пятницу в 15.52. Формула, не учитывающая время даст нам результат 2 рабочих дня. Однако, применяя в формуле данные времени мы получим 1,17 рабочих дней, что сделает данные более точными. Графиком работы сотрудников пока предлагаю пренебречь и условно считать, что они трудятся целые сутки без отдыха 😊.

Разобьем задачу на несколько шагов.

Проверка полноты данных

Сделаем проверку данных, что дата поступления запроса есть и задача уже решена (есть дата решения запроса).

IF ([Дата поступления запроса].[Date]=BLANK()||[Дата решения запроса].[Date]=BLANK(),BLANK()

Случай 1: запрос обработан в день поступления

Например, Дата поступления запроса=07.02.2022 11:54, Дата решения запроса=07.02.2022 15:02

Введем переменную для упрощения визуализации формулы. Данные времени нужно перевести к единой единице измерения- часы и далее результат поделить на 24 часа для получения числа рабочих дней.

 var x=(HOUR([Дата решения запроса])-HOUR([Дата поступления запроса])+MINUTE([Дата решения запроса])/60-MINUTE([Дата поступления запроса])/60)/24

Здесь переменная х будет равняться = ((15-11)+(2/60)-(54/60))/24=0,13 раб. дня.

Далее полная формула расчета при случае 1:

var x=(HOUR([Дата решения запроса])-HOUR([Дата поступления запроса])+MINUTE([Дата решения запроса])/60-MINUTE([Дата поступления запроса])/60)/24
return
IF([Дата поступления запроса].[Date]=[Дата решения запроса].[Date],x)

Важно: при сверке не забывать добавлять к ссылке на столбцы.

Случай 2: запрос пришел в выходной день

Здесь необходимо посчитать рабочие дни после поступления запроса + часы в день выполнения запроса.

Для начала проверим, является ли дата поступления рабочим днем c помощью производственного календаря (Таблица ‘Calendar’) и переменной Y.

var y = LOOKUPVALUE('Calendar'[рабдни],'Calendar'[Дата],[Дата поступления запроса].[Date])
return

Далее получаем число полных рабочих дней между датой поступления запроса и датой выполнения с помощью переменной Z:

var z= CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar','Calendar'[Дата].[Date]>[Дата поступления запроса].[Date]&&'Calendar'[Дата].[Date]<[Дата решения запроса] .[Date] &&'Calendar'[рабдни]=1)).

Итого расчет по случаю 2: запрос пришел в выходной день

//Проверка даты начала раб.дни

var y = LOOKUPVALUE('Calendar'[рабдни],'Calendar'[Дата],[Дата поступления запроса].[Date])

//полных рабочих дней между датами

var z= CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar','Calendar'[Дата].[Date]>[Дата поступления запроса].[Date]&&'Calendar'[Дата].[Date]<[Дата решения запроса].[Date] && 'Calendar'[рабдни]=1))
return

//случай 2 — запрос пришел в выходной

IF(y=0,(HOUR([Обр. Дата закрытия])+MINUTE([Обр. Дата закрытия])/60)/24+z,x+z)))

В данном примере DAX должен возвратить число 2,48= (11+38/60)/24 + 2 полных рабочих дня (10.01.2022+11.01.2022).

И самый распространённый случай 3: запрос пришел в рабочий день и завершен в рабочий день

Здесь следует посчитать полные рабочие дни начиная с даты поступления запроса (включительно) и учесть время дат поступления/завершения запроса.

Посчитаем вручную: 2(полные рабочие дни с даты поступления:14.01.2022 + 16.01.2022 )+ (11-16+(38-38)/24)=2-0,21=1,79 рабочий день

var x=(HOUR([Дата решения запроса])-HOUR([Дата поступления запроса])+MINUTE([Дата решения запроса])/60-MINUTE([Дата поступления запроса])/60)/24

//Проверка дата начала рд

var y = LOOKUPVALUE('Calendar'[рабдни],'Calendar'[Дата],[Дата поступления запроса].[Date])

//полных рабочих дней между датами

var z= CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar','Calendar'[Дата].[Date]>=[Дата поступления запроса].[Date] &&'Calendar'[Дата].[Date]<[Дата решения запроса].[Date] &&'Calendar'[рабдни]=1))
return
IF(y<>0,x+z)

Теперь объединим все случаи в одну формулу для столбца расчета:

var x=(HOUR([Дата решения запроса])-HOUR([Дата поступления запроса])+MINUTE([Дата решения запроса])/60-MINUTE([Дата поступления запроса])/60)/24

//Проверка дата начала рд

var y = LOOKUPVALUE('Calendar'[рабдни],'Calendar'[Дата],[Дата поступления запроса].[Date])

//полных рабочих дней между датами

var z= CALCULATE(COUNTROWS('Calendar'),FILTER('Calendar','Calendar'[Дата].[Date]>=[Дата поступления запроса].[Date] &&'Calendar'[Дата].[Date]<[Дата решения запроса].[Date] &&'Calendar'[рабдни]=1))
return

//Если дата начала или дата конца отсутствует, то пусто

IF([Дата поступления запроса].[Date]=BLANK()||[Дата решения запроса].[Date]=BLANK(),BLANK(),

// случай 1- обработка запроса день в день

IF([Дата поступления запроса].[Date]=[ Дата решения запроса].[Date],x,

//случай 2 — запрос пришел в выходной

IF(y=0,(HOUR([Дата решения запроса])+MINUTE([Дата решения запроса])/60)/24+z,x+z)))

Итак, я рассказала какие манипуляции можно проводить в DAX для расчета рабочих дней. Далее эти данные можно применять в комбинации с условным оператором IF, чтобы сразу маркировать запросы («В срок»/ «Не в срок») или устанавливать контрольный срок в зависимости от типа задачи.

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