Forum Discussion
Absence Data Plotted as Lost Days Per Month
Resolver IIIIt 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,
Thanks for this, I'm new to BI so will keep the blog in mind when posting.
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 Anonymous
The values for each month column are calculated in Excel with the below formula:
=IF(AND($B2<(H$1),$C2<(H$1)),0,IF($B2>EOMONTH(H$1,0),0,IF(AND($B2>=(H$1),$C2<=EOMONTH(H$1,0)),(NETWORKDAYS($B2,$C2)/5)*$D2,IF(AND($B2>=H$1,$C2>EOMONTH(H$1,0)),(NETWORKDAYS($B2,EOMONTH(H$1,0))/5)*$D2,IF(AND($B2<=H$1,$C2>=EOMONTH(H$1,0)),(NETWORKDAYS(H$1,EOMONTH(H$1,0))/5)*$D2,IF(AND($B2<=H$1,$C2<=EOMONTH(H$1,0)),(NETWORKDAYS(H$1,$C2)/5)*$D2,0))))))
B2 = Abs_Start_Date
H1 = Month Column Header (01/01/2016 etc. dispalyed as MMM/YY)
C2 = Abs_End_Date
D2 = Con_Work_Days
Hopfully that helps.
Thanks, G.