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

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