Calculate active subscribers with Initial and Expiration date with conditions

[MNaor]

Dear All,

I have a table with Subscribers, InitialDate and ExpirationDate. I need to present a chart of Active Subscribers during period (for example Month). Active subscriber is subsc. with no Expiration date or with Expiration date after the observed date.

It's mean - If I am looking to April 2017 i want to see subscribers with Initial Date < April 2017 and ExpirationDate NULL or after April 2017.

How can I do such measure? 

I have two questions - do i need to use YearToDate function in this case, if yes - how to use it?

Do i need to create a range of dates from the first date of initialdate to the last date of expirationdate? If yes - how to do it?

Thanks in advance

Michael

[v-yulgu-msft · Accepted solution]

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

[MNaor]

Dear @v-yulgu-msft, thank you so much for your detailed solution.

I've restored your solution and it looks like some data is missing.

Please see attached screenshot - in one table I choose data from the original table - ServicePeriod(right side).

On the left side I choose data from the created table Cross Join. You can see that the number of rows is not equal, for example Subscriber with EffectiveDay (Initial Date) 02/02/2016 from CrossJoin table is missing.

What do you think can cause this situation?

Thanks again.

Michael

[v-yulgu-msft]

Hi @MNaor,

If I am looking to April 2017 i want to see subscribers with Initial Date < April 2017 and ExpirationDate NULL or after April 2017

That is why the Subscriber with EffectiveDay (Initial Date) 02/02/2016 from CrossJoin table is missing. In my original formula, I filtered the cross join table with EffectiveDay<the first day of current month and ExpirationDay > the last day of current month. So, for EffectiveDay (Initial Date) 02/02/2016 is later than 01/02/2016, it won't be count in Febuary. For ExpirationDay 22/03/2016 is before 31/03/2016, it won't be considered in March.

If you need to count this record in both Febuary and March, please modify the formula for Corss Join table to below:

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

Best regards,
Yuliana Gu 

[g1davies]

Hi @v-yulgu-msft

Great Solution, I have a couple of questions:

1. My Bar chart has the months in alhpabetical order rather than in calendar order, can i change this?
2. I have subscriptions which are longer than a year and when i include them the graph doesn't work, can you help on what i need to change to enable me to do this?

Thanks

Gavin

[lg_analyst]

Hi @g1davies ,

I have the same issue: my initial and final dates cross through multiple years, ranging from Jan 2018 to today. Have you found any solution to the issue?

Thanks,

Enrico

[g1davies]

Yes, I solved it with, creating a date table and then doing the following.

Current Members 1 = CALCULATE (DISTINCTCOUNT ( 'Table 1'[ID] ),

FILTER (

GENERATE (

SUMMARIZE (

'Table 1',

'Table 1'[StartDate],

'Table 1'[EndDate]

),

DATESBETWEEN (

'DateTime'[Date],

'Table 1'[StartDate],

'Table 1'[EndDate] )),

CONTAINS ( VALUES ( 'Datetime'[Date] ), [Date], 'Datetime'[Date] )))

[MNaor]

Dear @v-yulgu-msft

I want to thank you once again for your professional help!
I've got what I wanted to get.

With Best Regards,

Michael

[v-yulgu-msft]

Hi @MNaor,

Glad to hear that you have got your desired result. Would you please kindly mark the corresponding reply as an answer or share your valid solution so that other community members having similar requirements can find the answer easily?

Regards,
Yuliana Gu