Average x function combined with date table

[frankhofmans]

hi all,

i have two tables:

Datetable

Date	Isworkingday
24-5-2024	1
25-05-2024	0
26-05-2024	0
27-05-2024	1
28-05-2024	1
29-05-2024	1
30-05-2024	1
31-05-2024	1
01-06-2024	0

And table: cases

CaseId	Startdate	Enddate
C001	01-02-2024	30-04-2024
C002	01-04-2024	10-04-2024
C003	01-05-2024	27-06-2024
C004	10-05-2024	30-06-2024
C005	01-06-2024	10-07-2024
C006	01-06-2024	03-06-2024

I want to calculate the following results:

Number of active cases: # of cases with at least 1 day in the selected period (startdatum <= max of selected date, enddate >= min of selected date)

Number of active days: sum of all days between startdate case (or min of selected date if startdate < min selected date) and enddate (or max of selected date if enddate > max selected date), but only the days that have a 1 in column isworkingday in the date table

Average duration: number of active days / number of active cases

Do you have a suggestion? the cases table has over 500k rows.

Thanks in advance,

Regards, Frank

[_AAndrade · Accepted solution]

Hi @frankhofmans,

I'm attaching a pbix file wiht my solution.