HR Analytics Using Power BI
Analyzing Employee Attendance and Leave Data
The human resource department is an integral part of every organization, and managing employee attendance and leave data is one of its core responsibilities. Analyzing this data can provide insights into employee performance, productivity, and overall well-being. Power BI is a powerful tool that can be used to analyze this data and gain valuable insights. In this blog post, we will explore how to use Power BI to analyze employee attendance and leave data.
The objective of this analysis is to identify the percentage of work from home (WFH), overall working days, the percentage of presence, and sick leave percentage. To achieve this, we will create measures and new columns in Power BI using DAX (Data Analysis Expressions).
Measures: Measures are calculations used to aggregate and analyze data. In our analysis, we will create three measures: Presence %, Total Working Days, SL%, and WFH%.
Presence %: Presence % is the percentage of days an employee is present at work. We can calculate Presence % using the formula below:
Presence % = DIVIDE([Present Days],’Measures Table’[Total Working Days],0)
Present Days: Present days are the days an employee is present at work, excluding work from home and leave days. We can calculate Present Days using the formula below:
Present Days = Var Presentdays= CALCULATE(COUNT(‘Final Data’[Value]),’Final Data’[Value]=”P”) RETURN Presentdays+[WFH Count]
Total Working Days: Total Working Days are the total number of working days in the given period, excluding weekends and holidays. We can calculate Total Working Days using the formula below:
WFH%: WFH% is the percentage of days an employee works from home. We can calculate WFH% using the formula below:
WFH% = DIVIDE([WFH Count],[Present Days],0)
WFH Count: WFH Count is the number of days an employee works from home. We can calculate WFH Count using the formula below:
WFH Count = SUM(‘Final Data’[WFH Count])
SL%: SL% is the percentage of days an employee is on sick leave. We can calculate SL% using the formula below:
SL % = DIVIDE([SL Count],[Total Working Days],0)
New Columns: New columns are created based on specific formulas to add more context to the data. In our analysis, we will create four new columns: WFH Count ,Month, SL Count,and Day of week .
WFH Count: WFH Count is the number of days an employee works from home. We can calculate WFH Count using the formula below:
WFH Count = SWITCH(TRUE(),’Final Data’[Value]=”WFH”,1,’Final Data’[Value]=”HWFH”,0.5,0)
SL Count: SL Count is the number of days an employee is on sick leave. We can calculate SL Count using the formula below:
SL Count = SWITCH(TRUE(),’Final Data’[Value]=”SL”,1,’Final Data’[Value]=”HSL”,0.5,0)
Month:
Formula — Month = STARTOFMONTH(‘Final Data’[Date])
Day of the Week:
Day of week = FORMAT(‘Final Data’[Date],”ddd”)
Conclusion: Using Power BI, we can analyze employee attendance and leave data to gain valuable insights into employee performance, productivity, and overall well-being. By creating measures and new columns in Power BI using DAX, we can calculate the percentage of WFH, overall working days, the percentage of presence, and sick leave percentage. This data can then be visualized and shared with stakeholders to make data-driven decisions that benefit the organization and its employees.
