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

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

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

Пример:

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

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

Календарь =
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», а подобное значение не должно учитываться в расчетах в соответствии с указанным периодом:

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

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

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

Рвав-рвав, наша машина времени успешно введена в эксплуатацию, хотя, конечно, смотреть «что у тебя было» — несколько грустновато…

Бегу проверять свои заначки :-)

Ваш Смайл