Рвав-рвав, собака Смайл поздравляет всех с началом отопительного сезона!
Сегодня мудрый не по годам собакен научит вас «глядеть в прошлое», то есть продемонстрирует на примере построение механизма ретроспективы.Пример:
- Таблица «Список сотрудников» содержит необходимые сведения обо всех сотрудниках, когда-либо работавших в организации:
- Календарь выделен в качестве отдельной таблицы, при этом он создан «динамическим образом», а именно:
Календарь =
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 записей, что соответствует значению меры, отображенному в карточке «Сотрудники, всего»:
Рвав-рвав, наша машина времени успешно введена в эксплуатацию, хотя, конечно, смотреть «что у тебя было» — несколько грустновато…
Бегу проверять свои заначки :-)
Ваш Смайл