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 )
)
