cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GJCockrill Frequent Visitor
Frequent Visitor

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 8
ssugar Member
Member

Re: Absence Data Plotted as Lost Days Per Month

Community Support Team
Community Support Team

Re: Absence Data Plotted as Lost Days Per Month

HI @GJCockrill,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
GJCockrill Frequent Visitor
Frequent Visitor

Re: Absence Data Plotted as Lost Days Per Month

Hi,

 

Please see our raw data sample:

Emp_NoAbs_Start_DateAbs_End_DateCon_Work_DaysAbs_CodeAbs_Duration_SAPAbs_Illness_Code
123427/11/201531/08/201632300191Maternity
567806/10/201630/05/201754001236

Cancer

 

I've added the manual columns below in excel to plot the lost days in months:

Emp_NoAbs_Start_DateAbs_End_DateCon_Work_DaysAbs_CodeAbs_Duration_SAPAbs_Illness_CodeJanuary 2016February 2016March 2016April 2016May 2016June 2016July 2016August 2016September 2016October 2016November 2016
123427/11/201531/08/201632300191Maternity12.612.613.812.613.213.212.613.8000
567806/10/201630/05/201754001236Cancer0000000001822

 

 

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.

GJCockrill Frequent Visitor
Frequent Visitor

Re: Absence Data Plotted as Lost Days Per Month

Hi,

 

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

Community Support Team
Community Support Team

Re: Absence Data Plotted as Lost Days Per Month

Hi @GJCockrill,

 

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

13.PNG

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
GJCockrill Frequent Visitor
Frequent Visitor

Re: Absence Data Plotted as Lost Days Per Month

Hi @v-shex-msft

 

The values for each month column are calculated in Excel with the below formula:

 

 

 

 

Community Support Team
Community Support Team

Re: Absence Data Plotted as Lost Days Per Month

Hi @GJCockrill,

 

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
GJCockrill Frequent Visitor
Frequent Visitor

Re: Absence Data Plotted as Lost Days Per Month

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.

Bridge Syntax Error.PNG

 

 

Can you help?

 

Many thanks,

Geneve

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors