MRR Calculation

mohit19,

About first and third requirement, create the following column in bills table. About the other requirements, do you want to drag a user list or site list? Do you want to calculate the result based on static months(6 months and 11 months) by evaluating user's subscription or site's subsscription? Please post expected result in table format based on sample data in your PBIX file.

subscriptionid = RELATED(subscriptions[subscriptionid])

Then create a new table using dax below.

Table = CALCULATETABLE(bills,FILTER(bills,bills[subscriptionid]>=4 && (bills[status]="paid"||bills[status]="voided")))

Create the following columns in the new table.

bill_end pf per subscription = CALCULATE(MAX('Table'[bill_at]),ALLEXCEPT('Table','Table'[Site id],'Table'[subscriptionid]))

index = RANKX(FILTER('Table','Table'[Site id]=EARLIER('Table'[Site id])),'Table'[bill_end pf per subscription],,ASC,Dense)

pre = var prev=CALCULATE(FIRSTNONBLANK('Table'[subscriptionid],1),FILTER('Table','Table'[Site id]=EARLIER('Table'[Site id])&&'Table'[index]=EARLIER('Table'[index])-1)) return IF(ISBLANK(prev),'Table'[subscriptionid],prev)

checkupgradeOrDowngrade = IF('Table'[subscriptionid]>'Table'[pre],"upgrade",IF('Table'[subscriptionid]<'Table'[pre],"downgrade",BLANK()))

For more details, please review attached PBIX file.

Regards,
Lydia