Forum Discussion

BBASARAN's avatar
BBASARAN
Regular Visitor
6 years ago
Solved

ABSENTEEISM TREND

I need to show the plant historical absenteeism trend in bar w/ illness etc. breakdown. Below, I've put a simple table, actually my data has more coloumns.

For example

- Janury 1st, value of bar chart will be 2, since 2 employees are absent.

- Janury 2nd, again 2

- Janury 3rd, it will be 3 and so on

 

My data has more than 50 K records after 2010. I could not find an easy way and need experts help ðŸ¤—

Any DAX, visual or etc suggests are wellcome.

 

EMPLOYEE NAME - ABS Start Time - ABS End Time - Illness
A - 1.01.2020 - 5.01.2020 - Infection
B - 1.01.2020 - 7.01.2020 - Musculoskeletal
C - 3.01.2020 - 10.01.2020 - Other
D - 5.01.2020 - 17.01.2020 - Musculoskeletal
B - 10.01.2020 - 20.01.2020 - Musculoskeletal
E - 21.01.2020 - 25.01.2020 - Infection
C - 20.01.2020 - 2.02.2020 - Musculoskeletal
A -18.01.2020 - 31.01.2020 - Infection
F - 3.01.2020 - 15.02.2020 - Other

Need Help
Show and Tell
Tips and Tricks
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, BBASARAN 

     

    You may modify the measure as below.

    Absent Num = 
var _date = SELECTEDVALUE('Calendar'[Date])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Employee Name],
    'Table'[ABS Start Time],
    'Table'[ABS End Time],
    'Table'[Illness],
    "flag",
    var _starttime = 'Table'[ABS Start Time]
    var _endtime = 'Table'[ABS End Time]
    return
    IF(
       _date>=_starttime&&_date<=_endtime,
       1,
       0
    )
)
return 
CALCULATE(
    DISTINCTCOUNT('Table'[Employee Name]),
    FILTER(
        tab,
        [flag] = 1
    )
)

     

    Then you can use 'Stacked column chart' to display the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, BBASARAN 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    Calendar(a calculated table):

    Calendar = CALENDARAUTO()

     

    There is no relationship between two tables. You may create a measure as below. 

    Absent Num = 
var _date = SELECTEDVALUE('Calendar'[Date])
var tab = 
SUMMARIZE(
    ALL('Table'),
    'Table'[Employee Name],
    'Table'[ABS Start Time],
    'Table'[ABS End Time],
    'Table'[Illness],
    "flag",
    var _starttime = 'Table'[ABS Start Time]
    var _endtime = 'Table'[ABS End Time]
    return
    IF(
       _date>=_starttime&&_date<=_endtime,
       1,
       0
    )
)
return 
CALCULATE(
    DISTINCTCOUNT('Table'[Employee Name]),
    FILTER(
        tab,
        [flag] = 1
    )
)

     

    For 1/1/2020 and 1/2/2020, there are employees including A,B. For1/3/2020, there are employees including A,B,C,F. Here is the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • BBASARAN's avatar
      BBASARAN
      Regular Visitor
      6 years ago

      v-alq-msft thank you for your reply.

       

      As I said, ı want to see daily absenteeism with illness breakdown in stacked coloumn. Your data shows daily absenteeism without breakdown.

      • v-alq-msft's avatar
        v-alq-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi, BBASARAN 

         

        You may modify the measure as below.

        Absent Num = 
var _date = SELECTEDVALUE('Calendar'[Date])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Employee Name],
    'Table'[ABS Start Time],
    'Table'[ABS End Time],
    'Table'[Illness],
    "flag",
    var _starttime = 'Table'[ABS Start Time]
    var _endtime = 'Table'[ABS End Time]
    return
    IF(
       _date>=_starttime&&_date<=_endtime,
       1,
       0
    )
)
return 
CALCULATE(
    DISTINCTCOUNT('Table'[Employee Name]),
    FILTER(
        tab,
        [flag] = 1
    )
)

         

        Then you can use 'Stacked column chart' to display the result.

         

        Best Regards

        Allan

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.