Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
User | Count |
---|---|
93 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |