Как считать текучесть персонала в Power BI / DAX

Как считать текучесть персонала в Power BI - это продолжение серии постов про расчет текучести персонала.

1. Как считать текучесть персонала в excel (кейс на примере конкретной компании)
2. Расчет показателей текучести персонала в Power Pivot (Excel) (автор Денис Костин)
3. Как считать текучесть персонала в R/Rstudio (кейс на примере конкретной компании)

Вводная

Считать текучесть в Power BI / DAX можно несколькими способами (также, как например, в excel можно получить какой-либо вариант разными путями), поэтому я показал лишь один из. И постарался, чтобы он не повторял пост Расчет показателей текучести персонала в Power Pivot (Excel) , поскольку в посте Дениса Костина и у меня используется DAX (англ. Data Analysis eXpressions — выражения для анализа данных) — формульный функциональный язык запросов, разрабатываемый Microsoft. Поэтому мы показываем по сути два варианта одного и того же.
Ничуть не претендую на оптимальный (простой) способ расчета текучести персонала в Power BI / DAX, но точно гарантирую, что этот способ соответствует расчету текучести персонала согласно законодательству:

Среднесписочная численность работников "за отчетный месяц исчисляется путем суммирования численности работников списочного состава за каждый календарный день отчетного месяца, т.е. с 1 по 30 или 31 число (для февраля - по 28 или 29 число), включая праздничные (нерабочие) и выходные дни, и деления полученной суммы на число календарных дней отчетного месяца.
Численность работников списочного состава за выходной или праздничный (нерабочий) день принимается равной списочной численности работников за предшествующий рабочий день. При наличии двух или более выходных или праздничных (нерабочих) дней подряд численность работников списочного состава за каждый из этих дней принимается равной численности работников списочного состава за рабочий день, предшествовавший выходным или праздничным (нерабочим) дням." Консультант плюс

Если вы читаете этот пост, значит вы уже имеете представление о  Power BI / DAX, поэтому в описании я не буду делать акцент на

1. базовых вещах типа загрузки данных, это основа, и вы должны этим владеть;
2. вариантах визуализации, цель поста - только расчет текучести персонала  Power BI / DAX, визуализацию я использую самую простую.

А также:

1. Я использую придуманные данные, там несколько десятков строк, я считаю это преимуществом, потому что можно проверить результат "на пальцах"
2. Я показываю решение в видео лекции, вы сможете увидеть и переписать формулы с видео, я не буду ничего закрывать.
3. Если же вы хотите получить код с расчетами и (по желанию) файл с данными, я готов это сделать за донат в 200 рублей, который пойдет на продвижение сервиса Калькулятор Времени поиска работы - пишите edvb()yandex.ru

Итак,

Лайфхак для тех, кто строит отчеты по стажу и возрасту в динамике (используя Power Pivot для Excel)

Всем привет, дорогие читатели.

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

1. сформировать настроенный отчет по персоналу на конец каждого месяца необходимого периода;
2. В каждом отчете за месяц добавить столбцы с расчетом стажа и возраста;
3. На отдельном листе рассчитать необходимый показатель (например, списочную численность) для каждой группы стажа и возраста за каждый период.

Как думаете, какие сложности приносит такой подход?

На мой взгляд как минимум следующие:

1. Необходимость формировать большое количество отдельных отчетов из HR системы;
2. Большое количество формул СУММЕСЛИ и СУММЕСЛИМН делает файл очень медленным;
3. При необходимости построения отчета в новом разрезе (например, пола или грейда) дополнительные затраты времени на создание новых формул.
4. Ошибки при расчете, т.к. не учитывается динамика в течение месяца.

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

В результате вы сможете на основе единственного отчета из HR-системы сформировать с помощью Power Pivot подобный расчет:

Для создания примера будем использовать файл из предыдущей статьи 

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

Стаж является изменяющейся во времени величиной, поэтому на каждую дату в отчете он должен быть рассчитан отдельно.

Решение будет состоять всего из 6 шагов:

1. Создание в Excel таблицы с группировками стажа
2. Импорт таблицы в Power Pivot
3. Создание вспомогательных мер для расчета среднесписочной численности
4. Создание вспомогательной меры для расчета стажа
5. Создание итоговой меры
6. Создание сводной таблицы с использованием нашей итоговой меры

Для начала создадим таблицу в Excel следующего содержания:

№	Группа стажа	Стаж MIN	Стаж MAX
1	до 3 мес	0	3
2	03 мес-1 год	3	12
3	1-2 лет	12	24
4	2-3 лет	24	36
5	3-5 лет	36	60
6	5-10 лет	60	120
7	более 10 лет	120	999

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

Расчет показателей текучести персонала в Power Pivot (Excel)

Всем, привет, дорогие друзья аналитики и не только.

Часто ли вам приходилось тратить уйму времени на переделку отчета, когда руководитель просил показать его еще "вот в таком разрезе" ? Используя стандартные средства Excel на переделку может уйти много времени, но это совершенно не обязательно.

Хочу поделиться с вами тем, как можно использовать инструменты бизнес-аналитики в Excel - Power pivot и Power Query для анализа текучести персонала. Использование этих мощных инструментов позволит вам существенно сэкономить время, затрачиваемое на подготовку аналитики в любых разрезах, имеющихся в исходных данных.

Ознакомиться в деталях с тем, что такое Power Pivot, функциональный язык DAX, Power Query и скриптовый язык M вы можете самостоятельно. Тут важно то, что, если вы хотите повысить свою продуктивность как аналитика, освоение этих инструментов поможет вам продвинуться в этом направлении. Ниже несколько ссылок на полезные источники:

1. http://baguzin.ru/wp/obrabotka-dannyh-s-pomoshhyu-modulya-powerpivot/ - введение в Power pivot
2. https://support.office.com/ru-ru/article/Справка-по-power-pivot-241aac41-92e3-4e46-ae58-2f2cd7dbcf4f - справка Power Pivot от Microsoft
3. https://msdn.microsoft.com/ru-ru/query-bi/dax/data-analysis-expressions-dax-reference - руководство по языку DAX.
4. https://habr.com/post/271019/ - введение в Power Query, краткий обзор возможностей
5. https://msdn.microsoft.com/query-bi/m/power-query-m-reference - полное описание языка M, используемого в Power Query (англ.)
6. https://needfordata.ru/blog/category/power-query - адаптированные переводы статей гуру Power Query Криса Вебба (Chris Webb).

Итак, в моем распоряжении имеется следующий набор данных https://cloud.mail.ru/public/Ffce/Gev6hBqWE. В файле отражены кадровые изменения (должность, подразделение, оклад, руководитель, город, филиал, центр затрат, количество занимаемых ставок и др.) по работникам за период с 2013 по 2016 годы. По одному работнику в таблице может быть несколько строк, каждая из которых имеет свой период начала и окончания действия. Периоды для одного работника не пересекаются. Также в крайних правых столбцах таблицы указаны причины увольнения и отношение компании к увольнению (сожалеем или не сожалеем). Отношение компании указано в связи с тем, что не все увольнения по собственному желанию являются нежелательной текучестью.

На базе этих данных я покажу как рассчитать среднесписочную численность, количество принятых и уволенных, Коэффициент оборота по выбытию, Коэффициент текучести, Скользящую среднюю текучесть за год. Итоговый файл со всеми формулами вы можете найти по ссылке https://cloud.mail.ru/public/7zD9/ysPgHSYcg.

Работу над моделью данных, в которой будут рассчитываться эти коэффициенты я построил в 2 шага:

1. Подготовительный - создание календаря для Power pivot с помощью Power Query.
2. Основной - создание формул, для расчета показателей текучести в Power Pivot.

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

Первым делом создадим новый лист Параметры, добавим на него таблицу Параметры, состоящую из наименования параметра и значения.

Начало и окончание периода я взял как начало первого года самого раннего события имеющегося набора данных и окончание последнего года. Во избежание проблем рекомендую при использовании календарей всегда захватывать год целиком. Последний параметр "Язык" указывает для какой локализации создавать календарь. Все вы знаете, что в разных странах могут использоваться различные форматы записи дат. Более подробно можете ознакомиться здесь (https://ru.wikipedia.org/wiki/Календарная_дата)

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

(ParameterName as text) =>

 let

     ParamSource = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content],

     ParamRow = Table.SelectRows(ParamSource, each ([Параметр]=ParameterName)),

     Value=

if Table.IsEmpty(ParamRow)=true

then null

else Record.Field(ParamRow{0},"Значение")

in

    Value

После этого наживаете Готово. Добавленная функция сохранится с именем Запрос1. Переименуем во что-то более понятное. В моем случае я заменил название на fnGetParameter

Эта функция необходима нам для того, чтобы получать из таблицы в Excel требуемых параметры.

Следующим шагом создадим наш календарь. Для этого, не выходя из окна Power Query создадим еще один пустой запрос. 
Нажмите правой кнопкой мыши в левой области интерфейса и выберите пункт как на скрине.

В открывшемся окне снова удалим содержимое и далее вставим текст, скопированный из файла Календарь PQ с параметрами задаваемыми из Excel.txt (https://cloud.mail.ru/public/vz7f/5Hygn66Q7). Созданный запрос переименуем в Calendar.

Наш календарь готов, и мы можем переходить к его загрузке в Power Pivot. Для этого в интерфейсе Power Query нажмите "Закрыть и загрузить-Закрыть и загрузить в". В следующем окне выберите "Создать только подключение" и в нижней части "Добавить эти данные в модель данных"

Не буду вдаваться в подробности описания кода Power Query. Если возникнут вопросы, в части PQ  прошу задавать в комментариях.

На следующем шаге мы создадим непосредственно расчетные формулы в Power Pivot. Сначала перейдите на лист База, в меню Excel перейдите на вкладку Power Pivot и нажмите добавить в модель данных, после этого нажмите кнопку управление - откроется интерфейс Power pivot

На листе Calendar добавьте столбец УникальныйНомерДня. Выберите первую из ячеек столбца и введите формулу:

= COUNTROWS (

    FILTER (

        ALL ( 'Calendar' );

        'Calendar'[Дата] <= EARLIER ( 'Calendar'[Дата] )

            && NOT (

                MONTH ( 'Calendar'[Дата] ) = 2

                    && DAY ( 'Calendar'[Дата] ) = 29

            )

    )

)

Этот столбец нам впоследствии пригодится для расчета скользящей средней за год.

Далее нам необходимо указать PP где у нас указаны даты (непрерывный список дней). Для этого перейдите на вкладку Конструктор -Пометить как таблицу дат. В открывшемся окне выберите наименование столбца, в котором наш список дат. В нашем случае столбец так и называется Дата.

Для тех, кто знаком с Power Pivot скажу, что мы не будем задавать связь между таблицами Calendar и Base в связи с расчетом скользящей средней и ряда других формул.