Difficult - 'Customer Base Figure' with StartDate and EndDate

[alextomlins]

Hi All,

Still very new to this and I'm not even sure if my logic is correct. We are a subscriptions business in which customers can choose to join or leave the subscribed digital products. When they do join we create a unique ID called a Unique Subscription ID (USID) and when they leave this Unique Subscription ID stays the same in the data. It is just a way of understanding the number of subscriptions we have irrespective of whether they are the same people.

Because of the way we have created the data every row is equal to one Unique subscription ID and every row details a start date and IF the customer has left it will then contain an end date.

What i want to get is a customer base figure detailing how many customer were part of the subscribed base at the point of time detailed in the dimensions. So if i use a month dimension - What is the count of customers active split by months.

I have attached a diagram that will hopefully detail several scenarios.

So i THINK the logic is;

Count number of USID (per calendar period split by chosen dimension) where start date is LESS THAN OR IN Calendar period AND end date is IN OR after the Calendar Period OR where End Date = Null

This is way beyond my abilities but having a base figure is very important to what i am doing at work.

Really hope that someone will be able to help with this as it's seriously holding me back. There may be a much better way of doing it. Maybe creating a new 'Customer Base' table from this table? I don't know if that's possible or even easier. Very Noob Sorry 

Help me Obi-Wan Qlikenobi, you're my only hope.

Thanks,

Alex