Calculate active subscribers with Initial and Expiration date with conditions

Hi MNaor,

Based on my understanding, below is the result I got in my test.

Sample data.

Create a calendar table and add three calculated columns.

Dim date =
CALENDAR (
    MIN ( 'Subscriber Table'[InitialDate] ),
    MAX ( 'Subscriber Table'[ExpirationDate] )
)

Month = 'Dim date'[Date].[Month]

First of a month =
CALCULATE (
    MIN ( 'Dim date'[Date] ),
    ALLEXCEPT ( 'Dim date', 'Dim date'[Date].[Month] )
)

Last of a month =
CALCULATE (
    MAX ( 'Dim date'[Date] ),
    ALLEXCEPT ( 'Dim date', 'Dim date'[Date].[Month] )
)

Summarize the above calendar table.

summarize date table =
SUMMARIZE (
    'Dim date',
    'Dim date'[Month],
    "first day", FIRSTNONBLANK ( 'Dim date'[First of a month], 1 ),
    "last day", FIRSTNONBLANK ( 'Dim date'[Last of a month], 1 )
)

Cross join source table and the summarized table.

Cross Join =
FILTER (
    CROSSJOIN ( 'Subscriber Table', 'summarize date table' ),
    'Subscriber Table'[InitialDate] < 'summarize date table'[first day]
        && (
            'Subscriber Table'[ExpirationDate] >= 'summarize date table'[last day]
                || 'Subscriber Table'[ExpirationDate] = BLANK ()
        )
)

Create a one to many relationship between table 'cross join' and 'summarize date table'.

Insert a bar chart visual, add [Month] from table 'summarize date table' into Axis section, and add [Subscribers] from table 'Cross Join' into Value section.

Best regards,

Yuliana Gu