calculate Absennteism in a monthly period

[JanuszC]

Hello,

I lost several weeks to try find how to count absenteeism.

I have a Absenteeism table [Emp ID], [start date], [end date], [absence reason]

calendar table and [working days] kolumn

my measure is

abs working days  = calculate(countrows('calendar'), datesbetween(calendar, absenteeism[start date], absenteeism[end date], Calendar [working days]=1, all(calendar))

relations beatween Absenteeism table and calendar table [Date] to [stard date] as one to many and calendar[date] to [end date] is switch off.

How to calculate monthly absenteeism?

Any support will appreciate

[v-jayw-msft]

Hi @JanuszC ,

Calculate the for each reason.

abs working days =
CALCULATE (
    COUNT ( 'calendar'[Date] ),
    FILTER (
        ALLSELECTED ( 'calendar' ),
        'calendar'[Date] >= SELECTEDVALUE ( Absenteeism[start date] )
            && 'calendar'[Date] <= SELECTEDVALUE ( Absenteeism[end date] )
            && 'calendar'[working days] = 1
    )
)

Then sum the abs working days for the same Emp ID.

Measure = SUMX(FILTER(ALLSELECTED(Absenteeism),Absenteeism[Emp ID]=SELECTEDVALUE(Absenteeism[Emp ID])),[abs working days])

Best Regards,

Jay

[JanuszC · Accepted solution]

Hi @v-jayw-msft,
The formula is working, it's couting working days beatween two dates but unfortunatelly doesn't split beatween month.
Data

Result

ID Employee #3 has 75 absent days in February - it is wrong
ID Employee #1 has 8 abs days in December - is wrong as well

The relations

I made similar formula bat teh result is the same

ABS working days1 = sumX(Absenteeism,

CALCULATE(

COUNTROWS('Calendar'),

DATESBETWEEN('Calendar'[Date], Absenteeism[Start Data], Absenteeism[End Data]),

'Calendar'[Working Days] = 1

))
I don't have any idea how to split working days beatween months

[JanuszC]

Maybe I did wrong assumption, e.g

Worker A had  sick 25-Dec-2021 till 5-Jan-2022 so his absenteeism was 10 calendar days or 8 working days (1st and 2nd of January was weekend) The graf should show 5 working days in December and 3 in January . The formula is showing 1 in December.

[JanuszC]

@Whitewater100thank You for your support, it is still doesn't work properly. I need a few days to check what is going on.

[Whitewater100]

Hi:

I beleive your measure for absence will work if you put month on visual axis.  You can add columns to your date table. You might even want a separate and unique employee table.

The calculated columns to add to your date table could be 

Month Name =  FORMAT('Calendar'[Date], "MMM")

Month & Year = FORMAT('Calendar'[Date],"YYYYMM"))

If you want monthly absense average (this will show correct month absence when visual axis is month as well)

Avg Month Absense = AVERAGEX(Calendar[Month & Year]), [Abs working days])

You can also calc absense = 

CALCULATE( COUNTROWS( absenteeism ),

FILTER( VALUES( absenteeism[start date] ), absenteeism[start date] <= MAX( 'Calendar'[Date] ) ),

FILTER( VALUES( absenteeism[end date]),  absenteeism[end date]>= MIN( 'Calendar'[Date] ))

I hope this helps..