Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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.

ssugar
Resolver III
Resolver III

Anonymous
Not applicable

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

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

 

Anonymous
Not applicable

Hi @v-shex-msft

 

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

 

 

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.