Calculate Contract Renewed Amount in second column when renewal date don't match/late renewal

[BJankic]

Hi,

I'm trying to calculate renewed amount in second column (renewed value) when renewal date don't match.

Original data : 

account_id	account_name	service_contract name	contract_stage	service_contract_value	renewed value	service_contract_start_date	service_contract_end_date
1	account_name_1	service_contract name_1	closed_won	$2,000.00		1/1/2022	1/1/2023
1	account_name_1	service_contract name_2	closed_won	$3,000.00		1/1/2023	1/1/2024
2	account_name_2	service_contract name_3	closed_won	$4,000.00		1/1/2022	1/1/2023
2	account_name_2	service_contract name_4	closed_won	$5,000.00		1/1/2023	1/1/2024
3	account_name_3	service_contract name_5	closed_won	$6,000.00		1/1/2022	1/1/2023
3	account_name_3	service_contract name_6	closed_won	$7,000.00		1/1/2023	1/1/2024
4	account_name_4	service_contract name_7	closed_won	$8,000.00		1/1/2022	1/1/2023
4	account_name_4	service_contract name_8	closed_won	$9,000.00		1/1/2023	1/1/2024
5	account_name_5	service_contract name_9	closed_won	$560,150.00		1/2/2022	1/1/2023
5	account_name_5	service_contract name_10	closed_won	$582,120.00		1/20/2023	1/19/2024
6	account_name_6	service_contract name_11	closed_won	$140,000.00		1/2/2022	1/1/2023
6	account_name_6	service_contract name_12	closed_won	$145,530.00		1/20/2023	1/19/2024

I have this calculation for now : 

Renewed Value = 
  VAR __AccountID = [account_id]
  VAR __Start = [service_contract_start]
  VAR __Next = MINX(FILTER(ALL('Table'),[account_id] = __AccountID && [service_contract_start] > __Start),[service_contract_start])
  VAR __Value = MINX(FILTER(ALL('Table'), [account_id] = __AccountID && [service_contract_start] = __Next), [service_contract_value]
RETURN
  __Value

But it is not reterning the value when dates are not matching for example it is not working for accunt name 5 and 6 and it is working for others.

Here is desired outcome : 

account_id	account_name	service_contract name	contract_stage	service_contract_value	renewed value	service_contract_start_date	service_contract_end_date
1	account_name_1	service_contract name_1	closed_won	$                           2,000.00	$           3,000.00	1/1/2022	1/1/2023
1	account_name_1	service_contract name_2	closed_won	$                           3,000.00		1/1/2023	1/1/2024
2	account_name_2	service_contract name_3	closed_won	$                           4,000.00	$           5,000.00	1/1/2022	1/1/2023
2	account_name_2	service_contract name_4	closed_won	$                           5,000.00		1/1/2023	1/1/2024
3	account_name_3	service_contract name_5	closed_won	$                           6,000.00	$           7,000.00	1/1/2022	1/1/2023
3	account_name_3	service_contract name_6	closed_won	$                           7,000.00		1/1/2023	1/1/2024
4	account_name_4	service_contract name_7	closed_won	$                           8,000.00	$           9,000.00	1/1/2022	1/1/2023
4	account_name_4	service_contract name_8	closed_won	$                           9,000.00		1/1/2023	1/1/2024
5	account_name_5	service_contract name_9	closed_won	$                       560,150.00	$      582,120.00	1/2/2022	1/1/2023
5	account_name_5	service_contract name_10	closed_won	$                       582,120.00		1/20/2023	1/19/2024
6	account_name_6	service_contract name_11	closed_won	$                       140,000.00	$      145,530.00	1/2/2022	1/1/2023
6	account_name_6	service_contract name_12	closed_won	$                       145,530.00		1/20/2023	1/19/2024

Thank you.

Bojan

[Jihwan_Kim · Accepted solution]

HI,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Renewed Value CC =
VAR __AccountID = [account_id]
VAR __Start = [service_contract_start_date]
VAR __Next =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            [account_id] = __AccountID
                && [service_contract_start_date] > __Start
        ),
        [service_contract_start_date]
    )
VAR __Value =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            [account_id] = __AccountID
                && [service_contract_start_date] = __Next
        ),
        [service_contract_value]
    )
RETURN
    __Value

[BJankic]

Hi @Jihwan_Kim this is realy helpful, but now I have different situation where products changed name(same product, new name in this year) and it is not catching that renewal. Do you have maybe quick solution for this?

Thank you.

Bojan

[Jihwan_Kim]

Hi,

Thank you for your message. May I ask how do your know those are the same produce even the names are different in each year? Perhaps, those have the same accountID? Or, those have the same key number?  

[BJankic]

Hi @Jihwan_Kim, this customer transitioned from legacy to new product lineup.

Account ID is the same, product ID is different.