Subscription Pattern in Power BI – DAX Measure for Active Subscribers or Open Tickets For All Dates

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

One of the common patterns of a data model is the subscription pattern. In this pattern, we have subscriptions (or tickets, or issues, or whatever else you want to call it) open date and close date. The way that this data is stored in a table makes it a bit challenging to get informative insight out of it. In this post (first of the series), I am going to explain about the subscription pattern and one of the common calculations needed for it; which is active subscribers at any given date, or open tickets at any given date.

What is a Subscription Pattern

If we talk about individual sales patterns, the data is stored mostly with one or more directly actionable date fields; an example is the order date. You can use the Order Date directly in a slicer and find all sales transactions that happened on that date. Subscription pattern is when we have a table with FROM and TO date fields. these also can be called as START and END, or OPEN and CLOSE. The FROM or TO fields together convey the meaning, not individually.

For example; subscribers to an online product or service, are stored in a table like this:

If I connect a date table to this table, let’s say to the Start Date, then what I am going to say? I can analyze subscriptions that have started on a specific day. If I connect a date table to End Date, then I can find out subscriptions that IF ended on a specific date. But what about all the other dates in between?

As an example, this is very common to ask what were the number of active subscription on 10th of Feb 2020? Or open tickets on that date?

So, the subscription pattern is when the two date fields convey a meaning together. You need to get insight FROM this field TO that field, you need the range in between, the meaning is not just the two values themselves, it is more than that.

Snapshot Table Can be Huge

One way to work with a subscription pattern is to build a snapshot table. Snapshot table is a table that has a record per day (even if that day had no action happening), per product, per customer, and per all other dimension’s key attributes. Although this can be an effective method, the snapshot table can be soon become one of the largest tables in your data model and consume a lot of resources. Imagine how big a snapshot table can get over time:

(365 days) * (20 years) * (10 products) * (1000 customers)= 73,000,000 records

And this is just for 10 products and 1000 customers. This table would be usually much bigger than this.

I will write another blog article and explain the method to create a snapshot table in that, However, I believe there is more value in doing this calculation using DAX measure, as you won’t need such a big snapshot table. So, here we talk about that second method.

Sample Dataset

My sample dataset, which you can download it down below this post, includes tables like below:

You already have seen the sample content of the Subscription table in the screenshot earlier. The date table is also created as a general date table using the script here.

You might wonder why the Date table is not connected to the Subscription table? Well, think about it this way: Date table should be connected to a table that individual date fields of that table have a meaning just by themselves. Otherwise, the relationship doesn’t make sense. For example; if I connect the Date table to Start Date, then I can have charts and visuals that tell me subscriptions that have STARTED at any given date.

Having a relationship from the Date table to the Start Date or End Date is absolutely fine, and I won’t discourage you from doing so. That relationship will help you to create charts tell you that this is when subscriptions have started and ended. However, that will never give you the active subscribers at any given date. That is what we are going to build through a DAX measure.

DAX Measure: Active Subscribers

The goal for this measure is to tell us how many subscribers have been active on any given date. Let’s go through that using an example;

If I want to see how many active subscribers we had on 10th of Feb 2020, then these are rules I need to apply:

  • Find all subscriptions that their End Date is blank, these are subscriptions that are active even just right now.
  • Find all subscriptions that their End Date is AFTER 10th of Feb 2020, these are subscriptions that are not active anymore but have been active at that date.
  • Both of the above operations should be done only on subscriptions that have started BEFORE 10th of Feb 2020.

My DAX measure then is the below script:

This is how the expression above works:

Getting the current date from the visual:

Getting the first start date in the subscription table:

Create a virtual table with all the rules that I mentioned above:

returning the count of rows of the virtual table

Because we want the calculation only to be done up until today’s date, checking the current date against today’s date, and also to be after the first start date in the subscription table

Altogether, this simple DAX measure helps us to get all we need without the need to a super large snapshot table:

You can see that I have used Subscription without the ALL in my expression, and the reason is that I don’t have any relationship between the Date table and the subscription table, If you have that type of relationship, then here, you would need to use ALL to change the context of filtering from relationship-based, to be custom based on your DAX code.

If you want to slice and dice this data by other dimensions, you can. Here you can select a customer and see the subscription history of this customer:

As you can see, this customer, was an active subscriber until the 14th of Feb, and then re-subscribed on the 1st of March.

Download Sample Power BI File

Download the sample Power BI report here:

Enter Your Email to download the file (required)

Video

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

10 thoughts on “Subscription Pattern in Power BI – DAX Measure for Active Subscribers or Open Tickets For All Dates

  • Hi Reza,

    this is a greag blog post. Thank you for that.
    I was wondering how you would set up the measure if I do have a relationshop between my date table and my subscription table. Because normally that would be case. I would have an active relationshop between the date and my start date, so I can measure how many subscribers have started on a particular date. Then I would also create an Inactive relationshop between the date and my end date so I can measure how many subscribers ended on a particular date (I use this relationshop with the userelationship function in DAX). So I need both of my relationships. But I would also want to know how many subscribers I had on every single date.

    So how would you use the ALL-function?

    Thanks in advance for your response.

    • Hi Peter,
      Here is the code using ALL;

      Active Subscribers =
      var _currDate=SELECTEDVALUE('Date'[Date])
      var _firstStartDate=MIN(Subscription[Start Date])
      var _subscribers=
      FILTER(
      ALL(Subscription),
      ( ISBLANK(Subscription[End Date]) // still subscribers
      || Subscription[End Date]>=_currDate ) // was active that date but ended now
      && Subscription[Start Date]< =_currDate // started before that date ) var _countRows=COUNTROWS(_subscribers) return if( _currDate<=TODAY() && _currDate>=_firstStartDate,
      if(ISBLANK(_countRows),0,_countRows)
      )

      Cheers
      Reza

      • Hi Reza,

        I didn’t realize it was that simple.
        It works flawless! Very nice solution which comes in handy in a los of cases.
        Does it work flawlessly on big models too?

        Thanks again and keep up all you good work in the community!

        Best regards,
        Peter

        • Hi Peter.
          You’re welcome.
          On a very huge table, it might be a bit slower, especially because the calculation happens runtime. However, comparing it with the snapshot scenario which you need to create a super huge table, this method usually performs better.
          Cheers
          Reza

  • Really great solution, thank you. I have added a Revenue column to your data and with a similar measure, replacing the countrows dax with a sum dax, I get the Revenue per day.

  • Hi Reza,
    I am trying to use this method to show active employees grouped using a time intelligence measure (prior month, prior month last year, etc) is joined to a date dimension table. I employee counts should also group by sites, so my employee data includes a site_id and joins on this to a sites table. The start & end dates are represented by hire_date and termination_date. Unfortunately the measure currently returns zeros. Would I need to change something to handle the sites and time intelligence groupings? Here is my measure DAX…
    Active_Employees =
    var _currDate=SELECTEDVALUE(‘Time Intelligence'[Date])
    var _firstHireDate=MIN(‘HR_Employees'[hireddate])
    var _employees=
    FILTER(
    ALL(‘HR_Employees’),
    ( ISBLANK(‘HR_Employees'[termdate]) // still employees
    || ‘HR_Employees'[termdate] >=_currDate ) // was active that date but terminated now
    && ‘HR_Employees'[hireddate] <=_currDate // started before that date
    )
    var _countRows=COUNTROWS(_employees)
    return
    if(
    _currDate=_firstHireDate,
    if(ISBLANK(_countRows),0,_countRows)
    )

    • Hi John
      Is your Date dimension Marked as Date table? or you are using the default date table of Power BI? depends on that, you might need to add “.[Date]” in some parts of the expression to get it working.
      Cheers
      Reza

      • Thanks for the Response Reza,
        My Time Intellgence DAX create a periods table with Prior Month, Prior Month -1, Prior Month Prior Year, Year to Date & Prior Year to Date. All the columns and measures in my data are by Date and are at a Site level, so they rollup to these groups. Unfortunetly Headcount is something we could get at a Date level but don’t want to summarize, we just want the end of period value. I am not sure how to acheive this?

Leave a Reply

Your email address will not be published. Required fields are marked *