Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BJankic
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_idaccount_nameservice_contract namecontract_stageservice_contract_valuerenewed valueservice_contract_start_dateservice_contract_end_date
1account_name_1service_contract name_1closed_won$2,000.00 1/1/20221/1/2023
1account_name_1service_contract name_2closed_won$3,000.00 1/1/20231/1/2024
2account_name_2service_contract name_3closed_won$4,000.00 1/1/20221/1/2023
2account_name_2service_contract name_4closed_won$5,000.00 1/1/20231/1/2024
3account_name_3service_contract name_5closed_won$6,000.00 1/1/20221/1/2023
3account_name_3service_contract name_6closed_won$7,000.00 1/1/20231/1/2024
4account_name_4service_contract name_7closed_won$8,000.00 1/1/20221/1/2023
4account_name_4service_contract name_8closed_won$9,000.00 1/1/20231/1/2024
5account_name_5service_contract name_9closed_won$560,150.00 1/2/20221/1/2023
5account_name_5service_contract name_10closed_won$582,120.00 1/20/20231/19/2024
6account_name_6service_contract name_11closed_won$140,000.00 1/2/20221/1/2023
6account_name_6service_contract name_12closed_won$145,530.00 1/20/20231/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_idaccount_nameservice_contract namecontract_stage service_contract_value  renewed value service_contract_start_dateservice_contract_end_date
1account_name_1service_contract name_1closed_won  $                           2,000.00   $           3,000.00 1/1/20221/1/2023
1account_name_1service_contract name_2closed_won  $                           3,000.00  1/1/20231/1/2024
2account_name_2service_contract name_3closed_won  $                           4,000.00   $           5,000.00 1/1/20221/1/2023
2account_name_2service_contract name_4closed_won  $                           5,000.00  1/1/20231/1/2024
3account_name_3service_contract name_5closed_won  $                           6,000.00   $           7,000.00 1/1/20221/1/2023
3account_name_3service_contract name_6closed_won  $                           7,000.00  1/1/20231/1/2024
4account_name_4service_contract name_7closed_won  $                           8,000.00   $           9,000.00 1/1/20221/1/2023
4account_name_4service_contract name_8closed_won  $                           9,000.00  1/1/20231/1/2024
5account_name_5service_contract name_9closed_won $                       560,150.00 $      582,120.001/2/20221/1/2023
5account_name_5service_contract name_10closed_won $                       582,120.00 1/20/20231/19/2024
6account_name_6service_contract name_11closed_won $                       140,000.00 $      145,530.001/2/20221/1/2023
6account_name_6service_contract name_12closed_won $                       145,530.00 1/20/20231/19/2024

 

Thank you.

 

Bojan

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

HI,

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

 

Jihwan_Kim_1-1679887015823.png

 

 

 

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.


Go to My LinkedIn Page


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

HI,

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

 

Jihwan_Kim_1-1679887015823.png

 

 

 

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.


Go to My LinkedIn Page


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

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.


Go to My LinkedIn Page


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

Account ID is the same, product ID is different.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.