cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Create measure for estimating renewal rate for subscriptions that could still potentially be renewed

Hello there, I'm having an issue with creating a specific measure for simulating renewal rates. Below is an example of the dataset I have.

SubscriptionIDSubsciptionDateRenewalDateSubscriptionQuarterRenewalQuarterDaysSinceToday
1015623-A20140401NULL2014_02NULL3235
1012156-B20181001201904012018_042019_021591
1018965-W20200401202012012020_022020_041043
1012648-L20210101NULL2021_01NULL768
1755002-M
 20220701
202302012022_032023_01222
8965223-Q20230101NULL2023_01NULL38

SubscriptionID is a unique identifier corresponding with each subscription, SubscriptionDate is the date that the original subscription occurred, RenewalDate is the date that a subscription was renewed (NULL means it hasn't been renewed), SubscriptionQuarter is the year and quarter the original subscription occurred (e.g. 2021_03 is the third quarter in 2021)RenewalQuarter is the year and quarter that the subscription was renewed in, and DaysSinceToday is the number of days that have passed since the original subscription date.

I'm trying to create a visual that displays the renewal rate(rr) during each quarter, as a percentage of all subscriptions that occurred during that quarter i.e. rr = (Number of subscriptions renewed during quarter)/(Total subscriptions during quarter). The visual should have the renewal quarter on the x-axis and the rr on the y-axis.

The measure for renewal rate is very easy to calculate, counting the number of rows where RenewalDate is not null and dividing by the total number of rows. However, quarters that are recent are inaccurate as the subscriptions haven't gone on long enough for them to need to be renewed. There is also a business rule that says that subscriptions can only be renewed within 18 months (or 548 days) after the original subscription date.

I've been trying for hours now to thus create a conditional measure for the renewal rate to solve this issue- If the quarter is more than 548 days ago, then simply output the rr, if it is within 548 days, then output the average rr of the two previous year's quarters (E.g. For quarter 2023_01, output the average rr from 2022_01 and 2021_01).

I'm not sure how to go about making the second condition, extracting the previous year's values and averaging them. I have tried filtering the data to only be getting the renewal rates from previous time periods, but I cannot get it to work properly. Does anyone have any suggestions? Any help would be great, thanks.

0 REPLIES 0

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.