Calculating number of days absent per month excluding weekends

[Anonymous]

Hi 

I'm trying to calculate the number of workforce absent days per month based on a start and end date and which takes into account absence spanning across a number of months.

I've come across the following code which is working perfectly for the number of calendar days, however, i would like to discount weekends and struggling on how to do it?

Any help greatly appreciated!

Calculated_Sick_days =

VAR MinDate = MIN ( Date_table[Date] )

VAR MaxDate = MAX ( Date_table[Date])

RETURN

   SUMX (

        Absence,

        VAR startDate =

            MAX ( Absence[Absence Start Date],MinDate)

        VAR endDate =

            IF (

                ISBLANK ( Absence[Absence End Date]),

                MaxDate,

                MIN ( Absence[Absence End Date], MaxDate ))

        RETURN

            (IF (

                endDate >= MaxDate,

                IF ( endDate >= startDate, DATEDIFF ( startDate, endDate + 1, DAY ) ),

                IF ( endDate >= startDate, DATEDIFF ( startDate, endDate + 1, DAY ) )

            )

    ))

[ppm1]

You can use the NETWORKDAYS function for that. Assuming the In date is when the person returns, here is an example measure.

WorkDaysOut = SUMX(T7, NETWORKDAYS(T7[Out], T7[In]-1))

Pat

[Anonymous]

Thanks Pat, but this doesn't work for people with an open ended absence. i.e. some people haven't returned to work yet and have a blank entry in the Absence end date.

I also want to show the the number of absences per month in a simple line chart by month and your measure just gives me a constant line

[ppm1]

Please try this version that uses TODAY() for any rows w/o a return date yet.

WorkDaysOut2 =
SUMX (
    T7,
    VAR maxdate =
        COALESCE ( T7[In], TODAY () )
    RETURN
        NETWORKDAYS ( T7[Out], maxdate - 1 )
)

Pat