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 | SubsciptionDate | RenewalDate | SubscriptionQuarter | RenewalQuarter | DaysSinceToday |
| 1015623-A | 20140401 | NULL | 2014_02 | NULL | 3235 |
| 1012156-B | 20181001 | 20190401 | 2018_04 | 2019_02 | 1591 |
| 1018965-W | 20200401 | 20201201 | 2020_02 | 2020_04 | 1043 |
| 1012648-L | 20210101 | NULL | 2021_01 | NULL | 768 |
| 1755002-M | | 20230201 | 2022_03 | 2023_01 | 222 |
| 8965223-Q | 20230101 | NULL | 2023_01 | NULL | 38 |
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.
