Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous

 

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

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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