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,

 

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

  • 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous,

       

      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