Forum Discussion
Absence Data Plotted as Lost Days Per Month
Hi,
We're trying to create an absence dashboard for our to HR leadership team. We have data from our HR system which depicts one absence per line per employee number (covers a period of 36 months).
I'm trying to obtain the lost days per month, but we have abence dates that straddle months (ie start 20/11/2018 and end 02/12/2018). As this is only on one line, I'm struggling to mark that line with a date (Nov/Dec) or calculate the number of lost days per month.
As a temp measure, I have manipulated the data in exel to show one line per absence, but then plot the lost days horizontally by month (one column per month). I've then exported this into Power BI and unpivoted the data so that there are now two columns (mm/yy and lost days) and multiple lines (one for each month on the excel doc).
I really don't want to have to manipulate the raw system data in excel prior to importing into Power BI so any tips on how BI can manipulate the data to plot lost days over months (even if one absence straddles two or more months), I would be grateful.
Many thanks in advance.
Geneve
8 Replies
ssugarResolver III
It would help if you provided some sample data.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Anonymous,
I add calculated column to your table store unique 'emp no' and 'abs code', then I create a expand table with emp/abs and detail date and a table with unique emp/abs value to link above tables.
Calculated column:
EMP/ABS = [Emp_No]&"/"&[Abs_Code]
Calculated tables:
Expand = VAR _calendar = CALENDAR ( MINX ( VALUES ( Absence[Abs_Start_Date] ), [Abs_Start_Date] ), MAXX ( VALUES ( Absence[Abs_End_Date] ), [Abs_End_Date] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Absence, _calendar ), [Date] >= [Abs_Start_Date] && [Date] <= [Abs_End_Date] ), "Emp/ABS", [EMP/ABS], "Detail Date", [Date] ) Bridge = VALUES(Absence[EMP/ABS])After these steps, I can create a matrix visual with emp no and abs code as row fields, date as column fields. I still not so sure how did you calculate the value fields, can you please explain more about this?
Regards,
Xiaoxin Sheng
Hi,
Please see our raw data sample:
Emp_No Abs_Start_Date Abs_End_Date Con_Work_Days Abs_Code Abs_Duration_SAP Abs_Illness_Code 1234 27/11/2015 31/08/2016 3 2300 191 Maternity 5678 06/10/2016 30/05/2017 5 4001 236 Cancer
I've added the manual columns below in excel to plot the lost days in months:
Emp_No Abs_Start_Date Abs_End_Date Con_Work_Days Abs_Code Abs_Duration_SAP Abs_Illness_Code January 2016 February 2016 March 2016 April 2016 May 2016 June 2016 July 2016 August 2016 September 2016 October 2016 November 2016 1234 27/11/2015 31/08/2016 3 2300 191 Maternity 12.6 12.6 13.8 12.6 13.2 13.2 12.6 13.8 0 0 0 5678 06/10/2016 30/05/2017 5 4001 236 Cancer 0 0 0 0 0 0 0 0 0 18 22 My end goal is to be able to plot visualisations that say X days lost to absence in X month over a period of time etc. I'm new to BI and so not sure on what/how I can go about this or whether the excel addition is actually the best way to go?
Many thanks.