Renewal rate with inactive relation

[shalabh]

Hi,

I am looking to get the renewal rate for licenses.

Renewal opportunity is qty expiring in given time period (say qtr). Expiry date is given by "End"

A lic is renewed if its start date is in the period of the end date.

Below is the sample table

Account ID	Product	Close	Start	End	Qty
1	A	01-Jan-23	01-Jan-23	01-Feb-23	10.00
1	A	01-Jan-23	01-Feb-23	01-May-23	10.00
4	A	06-Jan-23	06-Jan-23	01-Feb-23	8.00
1	B	25-Jan-23	25-Jan-23	10-Feb-23	40.00
4	A	05-Feb-23	05-Feb-23	01-May-23	2.00
1	A	01-Feb-23	01-Feb-23	05-Aug-23	10.00

From above, below is the explanation

Output	Product	Renewal Due	Renewal done	renewal Due comments	renewal done comments
Q1
Q2	A	18	12	10 from row 1+8 row 3	10 from row 2+2 from row 5
Q2	B	40	0	from row 5

Renewal is basis and account and prodcut ID. Final renewal will be rolled up by product.

I have a fiscal date table with active relation on close date and inactive on Start and End date

Note: I have used fiscal year with year starting Nov

Any direction will surely help please!

Regards

[shalabh]

@Jihwan_Kim 

I saw your solution to a similar ask on this thread https://community.fabric.microsoft.com/t5/Desktop/Calculate-Contract-Renewed-Amount-in-second-column...

I tried to use it but it doesn't meet my needs completely. Would you be able to have a look please and guide

Issues

1. If an account has renewal due say 10 in Q2, and the account has 2 transactions  in Q2 - one is 8 lic and other is 2 lic, the renewal done is 9. The model is not reflecting that

2. I want to show the renewal rate by quarter or time period selected by the user. Here I am unable to do that. The moment I select a time period, it shows blank

3. What I am looking to achieve is- KPI card of renewal rate (Renewal done/renewal opportunity), table showing top renewals done (account name, renewal due, renewal done, renewals lost (difference of opportunity and renewal done) ).

I have attached the pibx for reference https://www.dropbox.com/scl/fi/et845vxfkip4htsuirii8/test.pbix?rlkey=p60nhk7dm2r3wasrk7hlr5zdg&dl=0

Regards