Записки охотника

Ретроспектива

Тематические статьи
Рвав-рвав, собака Смайл поздравляет всех с началом отопительного сезона!

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

Пример:

  • Таблица "Список сотрудников" содержит необходимые сведения обо всех сотрудниках, когда-либо работавших в организации:

Таблица
Список сотрудников


  • Календарь выделен в качестве отдельной таблицы, при этом он создан "динамическим образом", а именно:

Календарь =
CALENDAR FIRSTDATE ( 'Список сотрудников'[Дата приема] ), TODAY () )

  • Столбец "Date" переименован в "Дата календаря".

Задача:

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

Рвав-рвав, предварительное описание составлено, теперь можно приступать к построению машины времени!

Процесс разработки:

  • На уровне Power Query cоздано представление таблицы "Список сотрудников", при помощи контекстного меню "Ссылка", имя представления задано как "Даты работы сотрудников".

Таблица
Список сотрудников

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

let
   EndList = [Дата увольнения] ?? DateTime.Date(DateTime.LocalNow())
in
   List.Dates([Дата приема], Duration.TotalDays(EndList - [Дата приема]) + 1, #duration(1, 0, 0, 0))

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

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

Таблица
Даты работы сотрудников

  • Следующим этапом является расстановка связей, схема модели представлена ниже:


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

Процесс визуализации данных:

  • Создать несколько элементов "Карточка" для визуализации статистической информации по персоналу, используя соответствующие меры.
  • Создать визуальный фильтр "Период", используя элемент "Срез", при этом в качестве значений использовать данные столбца “Дата календаря” таблицы "Календарь". При помощи данного элемента будет происходить процесс фильтрации дат с использованием двусторонней (!) связи.
  • Создать визуальный фильтр "Должность", используя элемент "Срез", при этом в качестве значений использовать данные соответствующего столбца таблицы "Список сотрудников". При помощи данного элемента будет происходить процесс фильтрации сотрудников согласно занимаемой должности.
  • Создать таблицу "Список сотрудников", используя элемент "Таблица", таблица является основным элементом отчета, и содержит полный список сотрудников.
  • Исходя из нашего набора данных, в период с 14 января по 31 декабря 2019 года в компании было 12 сотрудников, 2 сотрудника занимали позицию Старшего менеджера, а 10 – Менеджера, и при этом в настоящий момент в компании работает 8 человек:

Таблица
Полный список сотрудников

Пояснения к отчету:

  • Если все сделано верно, то при изменении конечной даты с "31.12.2019" на "13.02.2019" в таблице "Список сотрудников" будет отображено 2 записи, поскольку ближайшая дата приема 3-го сотрудника – это "14.02.2019", а подобное значение не должно учитываться в расчетах в соответствии с указанным периодом:

Таблица
Проверка отчета номер 1

То есть, по данным построенного отчета, в период с 14.01.2019 по 13.02.2019 в компании работало 2 человека на позиции Старшего менеджера, и при этом они оба продолжают работать в настоящий момент.

  • Фильтр "Период" работает в обе стороны, и вернув конечную дату в значение "сегодня" (или более позднее), а затем изменив значение начального отрезка времени с "14.01.2019" на "01.06.2019", в таблице "Список сотрудников" будет отображено 11 записей, поскольку один из сотрудников покинул компанию накануне (31.07.2019):

Таблица
Проверка отчета номер 2

Фильтрация данных при помощи дополнительных срезов (например, "Должность"), также производится корректным образом, поскольку, при нажатии кнопки "Менеджер", таблица "Список сотрудников" отображает 9 записей, что соответствует значению меры, отображенному в карточке "Сотрудники, всего":



Рвав-рвав, наша машина времени успешно введена в эксплуатацию, хотя, конечно, смотреть “что у тебя было” – несколько грустновато…
Бегу проверять свои заначки :-)
Ваш Смайл