topic Re: Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known in DAX Commands and Tips

Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known

Anonymous — Thu, 21 Jul 2022 15:35:11 GMT

Hi there,

I am creating a monthly absence report and just now are restricted to only calculating the total working days lost for absences which are closed (i.e., have an end date). I would like to also be able to calculate the total working days lost till date for those absences which are still ongoing (no end date) in that month.

My logic so far for the measure, is to count 1 working day lost for each date within the Absence Start Date and Absence End Date Range or count 1 working day lost for each date from the Absence Start Date until today if the end date is empty and then iterate through this count to sum the total working days lost but I am struggling to come up with the correct measure for this if anyone can help.

Here is a simple sample data structure:

Role	Start Date of Absence	End Date of Absence
Accountant	15/07/22	19/07/22
HR Manager	14/07/22
Data Analyst	01/07/22	11/07/22
Service Desk Analyst	20/07/22

Thanks!

Re: Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known

johnt75 — Thu, 21 Jul 2022 16:08:54 GMT

You could try

Work days lost = VAR minDate = MIN ( 'Date'[Date] ) VAR maxDate = MAX ( 'Date'[Date] ) RETURN SUMX ( 'Absences', VAR startDate = MAX ( 'Absences'[Start date], minDate ) VAR endDate = MIN ( 'Absences'[End date], maxDate ) RETURN NETWORKDAYS ( startDate, endDate ) )

That should work with date filters to show days lost in a given month.

Re: Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known

Anonymous — Thu, 21 Jul 2022 18:20:07 GMT

Hi John,

Thanks so much for this. It appears to work for the absences which have an end date but gives odd values for those which don't:

Re: Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known

Anonymous — Tue, 26 Jul 2022 08:05:30 GMT

Hi Anonymous ,

I suggest you to try this code to create a measure.

Measure = VAR _CALENDAR =ADDCOLUMNS( CALENDARAUTO(),"Weekday",WEEKDAY([Date],2)) VAR _FILTER = FILTER(_CALENDAR,[Date]>= MAX('Table'[Start Date of Absence])&&[Date]<=IF( MAX('Table'[End Date of Absence])=BLANK(),TODAY(),MAX('Table'[End Date of Absence]))) RETURN COUNTX(FILTER(_FILTER,NOT( [Weekday] IN {6,7})),[Date])

Result is as below.

Best Regards,
Rico Zhou

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

Re: Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known

Anonymous — Tue, 26 Jul 2022 08:21:45 GMT

That is really great! It works perfectly in table form. I have used a slight variation of this measure for annual absences too and it works great. Having said that, do you know of a small edit I could make which would allow me to view the total working days lost over the date time-series.

Re: Absenteeism - Calculating Total Working Days Lost when End Date of Absence Not Known

Anonymous — Tue, 26 Jul 2022 08:53:04 GMT

Hi Anonymous ,

I think you can try to create a new measure based on this measure.

Measure with total = SUMX(VALUES('Table'[Role]),[Measure])

Best Regards,
Rico Zhou

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