Calculate how many contracts are live for any given month

[DavidWaters]

Hi, I have got a difficult calculation which involves returning how many contracts are live for a given calendar table month, based on the contract start and expiry date.

Start	Expiry
03/02/2021	24/03/2021
19/02/2021	08/09/2021
05/03/2021	02/06/2021
06/10/2021	11/04/2022
07/10/2021	03/04/2022
01/10/2021	09/01/2022
27/11/2021	05/03/2022

I want to use the Calendar table month to then return the number of live contracts at these months:

Jan-21	0
Feb-21	2	2 started in Feb
Mar-21	3	1 started in Mar, the other 2 still live from Feb
Apr-21	2	1 expired in March so drops off
May-21	2	Just 2 still live
Jun-21	2	Just 2 still live
Jul-21	1	1 expires in June so drops off
Aug-21	1	Just 1 still live
Sep-21	1	Just 1 still live
Oct-21	3	3 new but 1 drops off a expired in Sept
Nov-21	4	1 new plus the other 3 still live
Dec-21	4	all 4 still live

I tried below measures but they don't work so I think I need another approach but have not resolved so if anyone can offer some wisdom that would be great!

# Open Contracts =
VAR MinDate = MIN( DataTable[StartDate] )
VAR MaxDate = MIN( DataTable[StartDate] )
RETURN COUNTROWS(
FILTER(
DataTable,
DataTable[StartDate] <= MaxDate
&& DataTable[ExpiryDate]>= MinDate
)
)

rolling12 =

CALCULATE(

[# Open Contracts],

DATESINPERIOD( 'Calendar Table'[Date], max('Calendar Table'[Date]), -12, MONTH )

)

[DavidWaters · Accepted solution]

Here is the solution, I eventually cobbled it together 

new calc = CALCULATE (

COUNTROWS (

FILTER (

Premium,

(

Premium[InceptionDate] IN VALUES ( 'Calendar Table'[Date] )

|| Premium[InceptionDate] < MIN ( 'Calendar Table'[Date] )

&& (

Premium[ExpiryDate] IN VALUES ( 'Calendar Table'[Date] )

|| ISBLANK ( Premium[ExpiryDate] )

|| Premium[ExpiryDate]> MAX ( 'Calendar Table'[Date] )

)

)

&& Premium[1] = 1

)

),

CROSSFILTER ( Premium[InceptionDate], 'Calendar Table'[Date], NONE )

[bwaldrupe]

What is Premium[1] supposed to represent here?

[v-jingzhang]

Hi @DavidWaters 

Glad you have solved it by yourself! You can accpet your reply as solution to close this thread. This can also make it easier to be found by other users who have similar problems. 

Best Regards,
Community Support Team _ Jing