Absence Data Plotted as Lost Days Per Month

[Anonymous]

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

[v-shex-msft]

HI @Anonymous,

Can you please provide more information to help us clarify your requirement?(e.g, data table structure, sample data, snapshots, expected result...)

Regards,

Xiaoxin Sheng

[Anonymous]

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.

[ssugar]

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

[Anonymous]

Hi,

Thanks for this, I'm new to BI so will keep the blog in mind when posting.

[v-shex-msft]

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

[Anonymous]

Hi @v-shex-msft,

I'm attempting to create the calculated table you suggested with:

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])

However I'm getting a sytax error on the 'bridge' element.

Can you help?

Many thanks,

Geneve

[Anonymous]

Hi @v-shex-msft

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.

[v-shex-msft]

Hi @Anonymous,

Your formula is complex and I try to format it as dax measure formula. You can try it if it works on matrix visual.(it think it should add more conditions to handle total level calculation)

Measure = 
VAR currStart =
    MIN ( [Abs_Start_Date] )
VAR currEnd =
    MAX ( [Abs_End_Date] )
VAR currWorkday =
    MAX ( [Con_Work_Days] )
VAR selected =
    MAX ( Expand[Detail Date] )
VAR workdays =
    COUNTROWS (
        FILTER ( CALENDAR ( currStart, currEnd ), WEEKDAY ( [Date], 2 ) <= 5 )
    )
VAR _lastdate =
    DATE ( YEAR ( selected ), MONTH ( selected ) + 1, 1 )
        - 1
RETURN
    IF (
        currStart < selected
            && currEnd < selected,
        0,
        IF (
            currStart > _lastdate,
            0,
            IF (
                currStart >= selected
                    && currEnd <= _lastdate,
                workdays / 5
                    * currWorkday,
                IF (
                    currStart >= selected
                        && currEnd > _lastdate,
                    COUNTROWS (
                        FILTER ( CALENDAR ( currStart, _lastdate ), WEEKDAY ( [Date], 2 ) <= 5 )
                    )
                        / 5
                        * currWorkday,
                    IF (
                        currStart <= selected
                            && currEnd >= _lastdate,
                        COUNTROWS (
                            FILTER ( CALENDAR ( selected, _lastdate ), WEEKDAY ( [Date], 2 ) <= 5 )
                        )
                            / 5
                            * currWorkday,
                        IF (
                            currStart <= selected
                                && currEnd <= _lastdate,
                            COUNTROWS (
                                FILTER ( CALENDAR ( selected, currEnd ), WEEKDAY ( [Date], 2 ) <= 5 )
                            )
                                / 5
                                * currWorkday,
                            0
                        )
                    )
                )
            )
        )
    )

Regards,

Xiaoxin Sheng