Calculate Contract Renewed Amount in Second Column

[BJankic]

Hi,

I'm trying to calculate renewed amount in second column (renewed value). This is original data information

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

This is the 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

Your help is much appreciated,

Thank you,

Bojan

[BJankic]

Thank you, @Greg_Deckler !

[Greg_Deckler · Accepted solution]

@BJankic Try something like:

Column = 
  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

[BJankic]

Hi @Greg_Deckler ,

I have a situation for this calculation that is not cathing the renewal value.

This one is okay :

These are not, probably because of the start date : 

Any chance you can update the formula?

Thank you.

Bojan

[Greg_Deckler]

@BJankic Is that scenario in the sample data?

[BJankic]

@Greg_Deckler It is not. Do you want me to send you new sample?

[Greg_Deckler]

@BJankic Yes please!

[BJankic]

@Greg_Deckler here it is sir.

Original : 

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

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

[BJankic]

@Greg_Deckler Did you manage to solve it, is there an issue with the dataset?

Thank you.

Bojan