Solved: Re: Number of days without overlap: absent employe...

mb769 · ‎10-08-2024

I couldn't find an answer for this problem about overlapping date ranges.

Goal: a measure counting the number of days for which NO substitution for an absent employee has been found.

(nb: dates are in dd-MM-YYYY format)

I have 2 (relevant) tables :

Absence : (an employee can be absent/substituded for only a part of their job, indicated by the Job column)

Absence_ID	Job	Employee	StartDate	EndData
1	A	A.B.	01-09-2024	30-09-2024
1	B	A.B.	01-09-2024	30-09-2024
2	A	C.D.	05-09-2024	06-09-2024
3	A	E.F.	10-10-2024	31-10-2024
3	C	E.F.	01-11-2024	31-12-2024
4	C	G.H.	11-11-2024	15-11-2024

Substitution

Substitution_ID	Employee_ID	SubstitutedAbsence	StartDate	EndDate
1	I.J.	1	10-09-2024	30-09-2024
2	K.L.	1	01-09-2024	05-09-2024
3	M.N.	3	15-10-2024	15-10-2024
4	O.P.	3	01-11-2024	10-11-2024
5	O.P.	4	11-11-2024	15-11-2024

These two tables are related with a many-to-many relationship

Abscence_ID *:* SubstitudedAbsence

I need a measure which can determine, for each absence ID, the number of days without any subsitution.
Result table of the above sample data would look like this:

Absence_ID	Days without substitution
1	4
2	2
3	70
4	0

I was able to calculate this by creating additional tables (a row for each date for each absence, joining with the substution table which I also split by day, and then counting rows), but this was too heavy on memory. Can anyone help me with finding a way how to calculate this?

v-xianjtan-msft · ‎10-10-2024

Hi @mb769

Please try to change the measure [TotalSubstitutionDays]:

TotalSubstitutionDays = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR SubstitutionPeriods = 
        FILTER(
            Substitution,
            Substitution[SubstitutedAbsence] = EARLIER(Absence[Absence_ID]) &&
            Substitution[StartDate] <= AbsenceEnd &&
            Substitution[EndDate] >= AbsenceStart
        )
    VAR CombinedPeriods = 
        ADDCOLUMNS(
            GENERATE(
                SubstitutionPeriods,
                VAR CurrentStart = MAX(Substitution[StartDate], AbsenceStart)
                VAR CurrentEnd = MIN(Substitution[EndDate], AbsenceEnd)
                RETURN
                SELECTCOLUMNS(
                    GENERATESERIES(CurrentStart, CurrentEnd, 1),
                    "Date", [Value]
                )
            ),
            "IsSubstituted", 1
        )
    VAR DistinctDates = 
        DISTINCT(
            SELECTCOLUMNS(
                CombinedPeriods,
                "Date", [Date]
            )
        )
    RETURN COUNTROWS(DistinctDates)
)

If only weekdays are considered, use the WEEKDAY() function in these meaures to filter out weekends.

1. Create a measure [TotalAbsenceDays(workdays)]:

TotalAbsenceDays(workdays) = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR AbsenceDates = 
        ADDCOLUMNS(
            GENERATESERIES(AbsenceStart, AbsenceEnd, 1),
            "IsAbsence", 1
        )
    VAR Workdays = 
        FILTER(
            AbsenceDates,
            WEEKDAY([Value], 2) < 6
        )
    RETURN COUNTROWS(Workdays)
)

2. Create a measure [TotalSubstitutionDays(workdays)]:

TotalSubstitutionDays(workdays) = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR SubstitutionPeriods = 
        FILTER(
            Substitution,
            Substitution[SubstitutedAbsence] = EARLIER(Absence[Absence_ID]) &&
            Substitution[StartDate] <= AbsenceEnd &&
            Substitution[EndDate] >= AbsenceStart
        )
    VAR CombinedPeriods = 
        ADDCOLUMNS(
            GENERATE(
                SubstitutionPeriods,
                VAR CurrentStart = MAX(Substitution[StartDate], AbsenceStart)
                VAR CurrentEnd = MIN(Substitution[EndDate], AbsenceEnd)
                RETURN
                SELECTCOLUMNS(
                    GENERATESERIES(CurrentStart, CurrentEnd, 1),
                    "Date", [Value]
                )
            ),
            "IsSubstituted", 1
        )
    VAR DistinctDates = 
        DISTINCT(
            SELECTCOLUMNS(
                CombinedPeriods,
                "Date", [Date]
            )
        )
    VAR Workdays = 
        FILTER(
            DistinctDates,
            WEEKDAY([Date], 2) < 6  
        )
    RETURN COUNTROWS(Workdays)
)

3. Create a summary table

ResultTable(workdays) = 
SUMMARIZE(
    Absence,
    Absence[Absence_ID],
    "Days without substitution", 
    [TotalAbsenceDays(workdays)] - [TotalSubstitutionDays(workdays)]
)

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

View solution in original post

v-xianjtan-msft · ‎10-08-2024

Hi @mb769

According to your sample data, should the result of “Days without substitution” with Abscence_ID = 3 be 72 instead of 70?

Try the following steps:

1. Create a measure to calculate the number of days absent for each Absence_ID:

TotalAbsenceDays = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    RETURN DATEDIFF(AbsenceStart, AbsenceEnd, DAY) + 1
)

2. Create a measure to calculate the number of substitution days for each Absence_ID:

TotalSubstitutionDays = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR SubstitutionPeriods = 
        FILTER(
            Substitution,
            Substitution[SubstitutedAbsence] = EARLIER(Absence[Absence_ID]) &&
            Substitution[StartDate] <= AbsenceEnd &&
            Substitution[EndDate] >= AbsenceStart
        )
    VAR SubstitutionDays = 
        SUMX(
            SubstitutionPeriods,
            DATEDIFF(
                MAX(Substitution[StartDate], AbsenceStart),
                MIN(Substitution[EndDate], AbsenceEnd),
                DAY
            ) + 1
        )
    RETURN SubstitutionDays
)

3. Create a measure to count the number of days without a substitution:

DaysWithoutSubstitution = Absence[TotalAbsenceDays] - Absence[TotalSubstitutionDays]

4. Create a summary table to show the number of days each Absence_ID had no substitutions:

ResultTable = 
SUMMARIZE(
    Absence,
    Absence[Absence_ID],
    "Days without substitution", [DaysWithoutSubstitution]
)

Here is the final result:

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

mb769 · ‎10-09-2024

Thank you, @v-xianjtan-msft , this works for a part.

But the difficulty (which I unfortunately didn't include in my sample data) is when 2 subsititution periods for the same absence overlap (which is possible, because it is theoretically for a different "job").

Let's change the substitution table to the following: (change is for Substitution_ID 2)

Substitution_ID	Employee_ID	SubstitutedAbsence	StartDate	EndDate
1	I.J.	1	10/09/2024	30/09/2024
2	K.L.	1	8/09/2024	15/09/2024
3	M.N.	3	15/10/2024	15/10/2024
4	O.P.	3	1/11/2024	10/11/2024
5	O.P.	4	11/11/2024	15/11/2024

The correct [DayswithoutSubsititution] would be 7 (1/9/2024-7/9/2024), but your measure would show less, because it counts the overlapping days (10/9/2024-14-9/2025) twice.

Any suggestion on how to solve that?

(To complicate matters even further, the follow-up question - but not urgent/necessary for the users - is: only weekdays should be counted as 'not substituted'... This is the reason why I initially created other tables in which I counted day by day.)

I'm thinking towards using the function GENERATESERIES.

v-xianjtan-msft · ‎10-10-2024

Hi @mb769

Please try to change the measure [TotalSubstitutionDays]:

TotalSubstitutionDays = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR SubstitutionPeriods = 
        FILTER(
            Substitution,
            Substitution[SubstitutedAbsence] = EARLIER(Absence[Absence_ID]) &&
            Substitution[StartDate] <= AbsenceEnd &&
            Substitution[EndDate] >= AbsenceStart
        )
    VAR CombinedPeriods = 
        ADDCOLUMNS(
            GENERATE(
                SubstitutionPeriods,
                VAR CurrentStart = MAX(Substitution[StartDate], AbsenceStart)
                VAR CurrentEnd = MIN(Substitution[EndDate], AbsenceEnd)
                RETURN
                SELECTCOLUMNS(
                    GENERATESERIES(CurrentStart, CurrentEnd, 1),
                    "Date", [Value]
                )
            ),
            "IsSubstituted", 1
        )
    VAR DistinctDates = 
        DISTINCT(
            SELECTCOLUMNS(
                CombinedPeriods,
                "Date", [Date]
            )
        )
    RETURN COUNTROWS(DistinctDates)
)

If only weekdays are considered, use the WEEKDAY() function in these meaures to filter out weekends.

1. Create a measure [TotalAbsenceDays(workdays)]:

TotalAbsenceDays(workdays) = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR AbsenceDates = 
        ADDCOLUMNS(
            GENERATESERIES(AbsenceStart, AbsenceEnd, 1),
            "IsAbsence", 1
        )
    VAR Workdays = 
        FILTER(
            AbsenceDates,
            WEEKDAY([Value], 2) < 6
        )
    RETURN COUNTROWS(Workdays)
)

2. Create a measure [TotalSubstitutionDays(workdays)]:

TotalSubstitutionDays(workdays) = 
SUMX(
    VALUES(Absence[Absence_ID]),
    VAR AbsenceStart = MINX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[StartDate])
    VAR AbsenceEnd = MAXX(FILTER(Absence, Absence[Absence_ID] = EARLIER(Absence[Absence_ID])), Absence[EndDate])
    VAR SubstitutionPeriods = 
        FILTER(
            Substitution,
            Substitution[SubstitutedAbsence] = EARLIER(Absence[Absence_ID]) &&
            Substitution[StartDate] <= AbsenceEnd &&
            Substitution[EndDate] >= AbsenceStart
        )
    VAR CombinedPeriods = 
        ADDCOLUMNS(
            GENERATE(
                SubstitutionPeriods,
                VAR CurrentStart = MAX(Substitution[StartDate], AbsenceStart)
                VAR CurrentEnd = MIN(Substitution[EndDate], AbsenceEnd)
                RETURN
                SELECTCOLUMNS(
                    GENERATESERIES(CurrentStart, CurrentEnd, 1),
                    "Date", [Value]
                )
            ),
            "IsSubstituted", 1
        )
    VAR DistinctDates = 
        DISTINCT(
            SELECTCOLUMNS(
                CombinedPeriods,
                "Date", [Date]
            )
        )
    VAR Workdays = 
        FILTER(
            DistinctDates,
            WEEKDAY([Date], 2) < 6  
        )
    RETURN COUNTROWS(Workdays)
)

3. Create a summary table

ResultTable(workdays) = 
SUMMARIZE(
    Absence,
    Absence[Absence_ID],
    "Days without substitution", 
    [TotalAbsenceDays(workdays)] - [TotalSubstitutionDays(workdays)]
)

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

mb769 · ‎10-10-2024

Perfect!

Thank you so much!

lbendlin · ‎10-08-2024

an employee can be absent/substituded for only a part of their job, indicated by the Job column

Yeah, no - that is too ambiguous. You'll have to drop that requirement to make it work.

Number of days without overlap: absent employees without substitute

Helpful resources

Join us at the Microsoft Fabric Community Conference

Power BI Monthly Update - February 2025

Fabric Community Update - March 2025

How to Get Your Question Answered Quickly

Join us at the 2025 Microsoft Fabric Community Conference

Number of days without overlap: absent employees without substitute

Helpful resources

Join us at the Microsoft Fabric Community Conference

Power BI Monthly Update - February 2025

Fabric Community Update - March 2025

How to Get Your Question Answered Quickly