Amortize values for a period

[Anonymous]

I have two tables. One with contract details and one with cancellation date and it is a one to many bidirectional relationship.

Table A

Customer	Start date	End date	Contract amount
A	01/02/2022	01/02/2023	100
A	01/02/2022	01/02/2023	50
B	01/02/2012	01/02/2023	50

Table B

Customer	Cancel Date
A	01/03/2023
B	01/03/2023

I have a measure called Annual Contract Value (ACV) that sums total value of all contracts lies in current contract period. So, for example, ACV for customer A is £150.

 I want to create a measure called revenue impact that amortize ACV for subsequent annual period. For example, Monthly revenue impact of customer A is £12.50. So, the revenue impact measure for customer A should display £12.50 from 01/02/2023 to 01/02/2024.

I will have a specific date selected through a date slicer on cancel date.

The ACV measure is working correctly, but the revenue is not outputting the same values as ACV and would appreciate any help.

ACV = calculate (sum (Table A [ Contract amount) , Table A [ Start Date] <= max(Table B [Cancel Date]) && Table A [End Date] > max(Table B [End Date]))

Revenue Impact =

var Range_Start = MIN(Table B [Cancel Date])

var Range_End = IF (MIN(Table B[ Cancel Date]) > min(Table A [End_Date]), EDATE(MIN(Table B[Cancel Date]),11), min(Table A [End_Date]))

var current_date = SELECTEDVALUE(Table B[Cancel Date])

VAR Monthly_ACV_Revenue = DIVIDE(Subscription_ACV , 12)

return

IF(current_date >= Range_Start && current_date <= Range_End , Monthly_ACV_Revenue)

[Sahir_Maharaj]

Hello @Anonymous,

Can you please try this:

Revenue Impact =
VAR Range_Start = MIN('Table B'[Cancel Date])
VAR Range_End = IF(MIN('Table B'[Cancel Date]) > MIN('Table A'[End date]), EDATE(MIN('Table B'[Cancel Date]), 12), MIN('Table A'[End date]))
VAR Monthly_ACV_Revenue = DIVIDE([ACV], 12)
VAR Current_Date = SELECTEDVALUE('Table B'[Cancel Date])
RETURN
    IF(Current_Date >= Range_Start && Current_Date <= Range_End, Monthly_ACV_Revenue, BLANK())