New, active and closed Contracts

[NewPBIe]

Hello everybody. I've got a Date Table (Calender) and a Table with IDs, Start date and End date for some Contracts (Data).

I need to answer three different questions pro each Period (fiscal year october - september):

1. how many new contracts  (in this period)

2. how many active contracts (from this and the previus periods)

3. how many closed contracts (in this period)

Now 1 and 3 are easy: I connected Calender[Date] with both [Start Date] and [End Date] from Data and selected the correct relastionship between the Tables. 

I am stuck with 2, since I can' t ignore the active relationship and every measure keeps counting the new contracts and not the active ones. 

Sample Data / Input:

ID	START	END
A	16.10.2020	30.08.2021
B	16.10.2020	31.01.2021
C	21.12.2020	31.04.2021
D	13.01.2021
E	08.03.2021	30.06.2021
F	01.09.2021	30.09.2021

Output:

	New	Closed	Active	which IDs should be counted in active
Okt. 20	2	0	2	A;B
Nov. 20	0	0	2	A;B
Dez. 20	1	0	3	A;B;C
Jan. 21	1	1	4	A;B;C;D
Feb. 21	0	0	3	A;C;D
Mrz. 21	1	0	4	A;C;D;E
Apr. 21	0	1	4	A;C;D;E
Mai. 21	0	0	3	A;D;E
Jun. 21	0	1	3	A;D;E
Jul. 21	0	0	2	A;D
Aug. 21	0	1	2	A;D
Sep. 21	1	1	2	D;F

What am I doing wrong? Thank you in advance!

[johnt75 · Accepted solution]

Try

Active contracts =
VAR ReferenceDate =
    MAX ( 'Calendar'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        REMOVEFILTERS ( 'Date' ),
        'Table'[Start date] <= ReferenceDate
            && (
                ISBLANK ( 'Table'[End date] )
                    || 'Table'[End date] > ReferenceDate
            )
    )
RETURN
    Result