Measure for number of absences on any date

[Vantage111]

I have a table called ABSENCES with a column for start_date and end_date when an employee has been absent.

I would like to graph the total number of staff absence on any given date using my CalendarTAB[date] table.

I would also like to produce a 12 Month moving average of Monthly Absences.

Thanks in expectation 😉

[v-yiruan-msft]

Hi @Vantage111 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

AbsenceCount = 
CALCULATE(
    DISTINCTCOUNT(ABSENCES[staff]),
    FILTER(
        ABSENCES,
        ABSENCES[start_date] <= MAX(CalendarTAB[date]) && 
        ABSENCES[end_date] >= MAX(CalendarTAB[date])
    )
)

MovingAvgAbsences = 
VAR _date =
    SELECTEDVALUE ( 'CalendarTAB'[Date] )
VAR _eodate =
    EOMONTH ( _date, -11 )
VAR _sdate =
    DATE ( YEAR ( _eodate ), MONTH ( _eodate ), 1 )
RETURN
    AVERAGEX (
        FILTER (
            ALLSELECTED ( CalendarTAB ),
            'CalendarTAB'[Date] >= _sdate
                && 'CalendarTAB'[Date] <= EOMONTH ( _date, 0 )
        ),
        [AbsenceCount]
    )

Best Regards

[Vantage111]

Thnks for the response @v-yiruan-msft and @BA_Pete . I think so far we have all got to roughly the same point, but not quite what I'm looking for - applied both solutions to my data. I think what I am seeing is only the first day of absence (or last) rather than all of the days in that absense instance.

See my snip below.

S1 is off for two days, at the same time S2 is off for three days etc, what I am looking to display are those combined absences_on_the_day and the moving average of that number - there is another column in the ABSENCES table that may help, deducted which is the number of days deducted for this absence - S1=2, S2=3, S3=1 etc.

[BA_Pete]

Hi @Vantage111 ,

You can use the following MEASURE structure to work out basically any calculation at a given point in time:

_noofAbsentAtDate = 
VAR __cDate = MAX(CalendarTAB[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(ABSENCES[EmployeeNumber]),
    FILTER(
        ABSENCES,
        ABSENCES[start_date] <= __cDate
        && ( ABSENCES[end_date] > __cDate || ISBLANK(ABSENCES[end_date]) )
    )
)

You need to use a column from your CalendarTAB table as the axis in any visual along with this type of measure for it to work. You also need to ensure that your CalendarTAB table IS NOT related to your ABSENCES table or that the relationship is inactive. If the table must be actively related, you can use CROSSFILTER to switch it off, something like this:

_noofAbsentAtDate = 
VAR __cDate = MAX(CalendarTAB[date])
RETURN
CALCULATE(
    CALCULATE(
        DISTINCTCOUNT(ABSENCES[EmployeeNumber]),
        FILTER(
            ABSENCES,
            ABSENCES[start_date] <= __cDate
            && ( ABSENCES[end_date] > __cDate || ISBLANK(ABSENCES[end_date]) )
        ),
    CROSSFILTER(CalendarTAB[date], ABSENCES[YourRelatedColumn], None)
)

In terms of your 12 Month average, you can just adjust the calculation that's evaluated and the date ranges that are filtered to get what you want.

Pete