cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

4 REPLIES 4
Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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

Super User

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?

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

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

Account ID is the same, product ID is different.