Count 1 for every month that a row does not appear in a range of dates from table 2

[VV24]

Dear all,

Im facing the following problem:

I have a table with employees and their start and enddate of their contract (if blank then still active). See example below:

EmployeeNoEmployee Contract startEmployee Contract end

10500	1-11-2017
10550	1-12-2017	30-9-2018
10600	1-1-2018	31-12-2018
10650	1-2-2018	31-12-2018
10700	1-3-2018
10750	1-4-2018
10800	1-5-2018
10900	1-4-2018
10950	1-2-2018	31-1-2019
11560	1-8-2018
11900	1-9-2018

Next I have a table with absence data, per absence occurence (from start to end) i have the start time of absence and the end time of the absence (if blank, then still absent).

AbsenceKeyEmployeeNoAbsence startAbsence end

1	10500	1-1-2018	15-11-2018
2	10600	5-3-2018	10-8-2018
3	10900	6-3-2018
4	10950	10-4-2018	11-4-2018
5	11560	1-11-2018	5-2-2019
6	11900	25-2-2019
7	10950	15-5-2018	25-8-2018

What I would like to check, is the employees who have not been sick in the last twelve months, per month.

Required outcome example:

MonthEmployeeCount

nov-17	10500	1
dec-17	10500	1
jan-18	10550	1
feb-18	10550	1
mrt-18	10550	1
apr-18	10550	1
mei-18	10550	1
jun-18	10550	1
jul-18	10550	1
aug-18	10550	1
sep-18	10550	1
jan-18	10600	1
feb-18	10600	1
…..

and so on.

[AlB · Accepted solution]

Hi @VV24 

Try this (see it at work in the file attached)

1. Create a Calendar table so that we can show the month-year as requested:

CalendarT = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2016; 01; 01 ); DATE ( 2019; 12; 31 ) );
    "MonthYear"; FORMAT ( [Date]; "mmm" ) & "-"
        & YEAR ( [Date] );
    "MYN"; YEAR ( [Date] ) * 100
        + MONTH ( [Date] )
)

2. Create a calculated table:

ResultTable =
ADDCOLUMNS (
    CROSSJOIN (
        CALCULATETABLE (
            SUMMARIZE ( CalendarT; CalendarT[MonthYear]; CalendarT[MYN] );
            YEAR ( CalendarT[Date] ) >= 2017
        );
        DISTINCT ( Employee[EmployeeNo] )
    );
    "_Flag";
    VAR _FirstMYN =
        CALCULATE (
            MIN ( CalendarT[MYN] );
            FILTER ( CalendarT; CalendarT[MYN] > EARLIER ( [MYN] ) - 100 )
        )
    VAR _LastMYN = [MYN]
    VAR _Last12Months =
        CALCULATETABLE (
            DISTINCT ( CalendarT[Date] );
            FILTER ( CalendarT; CalendarT[MYN] >= _FirstMYN && CalendarT[MYN] <= _LastMYN )
        )
    VAR _FirstDayLast12Months = FIRSTNONBLANK ( _Last12Months; 1 )
    VAR _LastDayLast12Months = LASTNONBLANK ( _Last12Months; 1 )
    VAR _EmployeeStartDate = LOOKUPVALUE (Employee[Employee Contract start]; Employee[EmployeeNo]; [EmployeeNo])
    VAR _EmployeeStartMY = LOOKUPVALUE ( CalendarT[MYN]; CalendarT[Date]; _EmployeeStartDate )
    VAR _EmployeeEndDate =
        LOOKUPVALUE (
            Employee[Employee Contract end];
            Employee[EmployeeNo]; [EmployeeNo]
        )
    VAR _EmployeeEndMY = LOOKUPVALUE ( CalendarT[MYN]; CalendarT[Date]; _EmployeeEndDate )
    VAR _EmployedLast12Months =
        _EmployeeStartDate <= _FirstDayLast12Months
            && ( _EmployeeStartDate >= _LastDayLast12Months|| ISBLANK ( _LastDayLast12Months ))
    VAR _AbsenceTableCurrentEmployee =
        ADDCOLUMNS (
            FILTER ( Absence; Absence[EmployeeNo] = EARLIER ( [EmployeeNo] ) );
            "_Absent?"; COUNTROWS (
                INTERSECT (
                    GENERATESERIES (
                        Absence[Absence start];
                        IF (
                            ISBLANK ( Absence[Absence end] );
                            _LastDayLast12Months;
                            Absence[Absence end]
                        )
                    );
                    _Last12Months
                )
            ) > 0
        )
    VAR _WasAbsent =
        SUMX ( _AbsenceTableCurrentEmployee; [_Absent?] * 1 ) > 0
    RETURN
        IF (
            NOT _WasAbsent
                && (_EmployeeStartMY <= [MYN] && ( _EmployeeEndMY >= [MYN] || ISBLANK ( _EmployeeEndMY )));
            1;
            0
        )
)

3. Place ResultTable[EmployeeNo], ResultTable[MonthYear] and ResultTable[_Flag] in a table visual. Make sure they all are displayed with 'Don't summarize'

To wrap it up, review the behavior at the edges as it might not be exactly what you want. You can update the code needed. 

Cheers

[VV24]

Thanks AIB,

I tried to rebuild it with, with real data but i get an error saying that "the arguments in the GenerateSeries function cannot be blank."

Do you know how this comes?

[AlB]

It's probably the first argument, i.e. the absence start date. Are there any blanks? I assumed there wouldn't.

What does a blank in the absence start date mean? What is the expected behavior? One option would be doing a check on the first argument similar to what we already do in the second argument):  

GENERATESERIES (
               IF ( ISBLANK ( Absence[Absence start] ); _FirstDayLast12Months; Absence[Absence start] );
               IF (
                            ISBLANK ( Absence[Absence end] );
                            _LastDayLast12Months;
                            Absence[Absence end]
                        )
                    );

 You can modify that depending on the behavior you want. It might be wise to eliminate blanks in the original data altogether.

Otherwise, if you share your real data I'll try to have a look.

[VV24]

So we are getting there! It does work now, one problem iam facing is that my datetable starts at year 2000, so all the employees even those with a contract start date later than 2000 are in a row per month (with flag = 0), but I only want to have a row if their contract started.