number of active contracts on continious time line

[LinasG]

Hi,

I am looking to calculate and visualise number of active contracts on continious time x axis. Looking at the chart I should be able to identify min and max number of active contracts at any day.

Active from	Active to	Contract
10/01/2023	20/01/2023	A
12/01/2023	23/03/2023	B
20/01/2023	31/01/2023	C
23/01/2023	05/04/2023	D
24/01/2023	08/02/2023	E
06/02/2023	24/03/2023	F
08/02/2023	23/02/2023	G

Would anyone be able to help or direct me where to start?

[LinasG]

I nneeded to create calendar column first.

Formula worked without MAX before ('Date'[Date]

[johnt75 · Accepted solution]

You can use

Active contracts =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Active from] <= MaxDate
            && 'Table'[Active to] >= MaxDate
    )
RETURN
    Result

Make sure that there are no active relationships between your date table and fact table, or if there are you will need to add REMOVEFILTERS('Date') into the CALCULATE