Forum Discussion

MNaor's avatar
MNaor
Regular Visitor
9 years ago
Solved

Calculate active subscribers with Initial and Expiration date with conditions

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 E...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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's avatar
MNaor
Regular Visitor
9 years ago

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's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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 

  • MNaor's avatar
    MNaor
    Regular Visitor
    9 years ago

    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's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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

  • g1davies's avatar
    g1davies
    Icon for Helper I rankHelper I
    9 years ago

    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's avatar
      lg_analyst
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        g1davies
        Icon for Helper I rankHelper I
        6 years ago

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