topic Re: calculate Absennteism in a monthly period in DAX Commands and Tips

calculate Absennteism in a monthly period

JanuszC — Mon, 11 Apr 2022 13:10:01 GMT

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

Re: calculate Absennteism in a monthly period

Whitewater100 — Mon, 11 Apr 2022 14:15:29 GMT

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

Re: calculate Absennteism in a monthly period

JanuszC — Tue, 12 Apr 2022 13:01:18 GMT

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

Re: calculate Absennteism in a monthly period

JanuszC — Wed, 13 Apr 2022 08:10:29 GMT

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.

Re: calculate Absennteism in a monthly period

Anonymous — Thu, 14 Apr 2022 16:01:31 GMT

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

Re: calculate Absennteism in a monthly period

JanuszC — Fri, 15 Apr 2022 11:42:24 GMT

Hi Anonymous,
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