Power Bi - Count of subscriber with variable start/end dates per quarter?

[Anonymous]

Hello-

I have a list where each row is an individual subscription record. The subscribers can have variable start and end dates, but I would like to show how many subscribers are "active" at the end of each quarter. Users

My data looks like this: (null end date means the subscription is still going)

UserID	StartDate	EndDate
1	7/1/2022	11/3/2022
2	7/1/2022	2/1/2023
3	10/5/2020	null
4	1/1/2020	null
1	2/2/2023	null

I'd like to create a table similar to:

	Total_Subscribers
Q1 (7/1/22-9/30/22)	4
Q2 (10/1/22-12/31/22)	3
Q3 (1/1/23-3/30/23)	3
Q4 (4/1/23 - 6/30/23)	3

Can anyone help me to accomplish this? 

[ThxAlot]

SUBSCRIPTON.pbix

[jdbuchanan71]

@Anonymous 

You will need a Dates tables that is not connected to your Subscriptions table which you can make with this DAX.

Dates = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",YEAR([Date]),
    "Quarter","Q" & QUARTER([Date]),
    "Year Quarter", YEAR([Date])&"-Q"&QUARTER([Date])
)

Then a measure to calculate the unique Subscriptions[UserID] on the last day of the period you are looking at.

Ending Subscribers = 
VAR _End = MAX ( Dates[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( Subscriptions[UserID] ),
    Subscriptions[StartDate] <= _End,
    ( Subscriptions[EndDate] >= _End || ISBLANK ( Subscriptions[EndDate] ) )
)

Then you add the Year Quarter field from the Dates table and the measure to your visual.

I have added my sample file for you to look at.