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 ch...
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.

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.

    • BBASARAN's avatar
      BBASARAN
      Regular Visitor
      6 years ago

      it works. Thank you for your reply.

       

      Is there a way to sum that measure between 2 given dates?