Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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.



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.


Helpful resources

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors