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

[JK_PowerBINew]

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!

[JK_PowerBINew]

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.

[v-rzhou-msft]

Hi @JK_PowerBINew ,

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.

[johnt75]

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.

[JK_PowerBINew]

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:

[v-rzhou-msft · Accepted solution]

Hi @JK_PowerBINew ,

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.