Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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
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
@BJankic Is that scenario in the sample data?
@BJankic Yes please!
@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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |