Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
I have a table with all opportunity changes. Based on the change date of the opportunity values, I need to get the historical evolution of all opportunities over time. See how opportunity values change over time. How much was 100,90,60,30,1,0 after months depending on how they changed.
So, this one is changed at 1/6/2023 and until today's date is 100 because it has not been changed.
Another one is at the value 10 from 1/11/2023 until 31/1/2024 and from 1/2/2024 until today it is at the value of 0
I need to get a table that fills the value values for each opportunity from the first change date to today. At the last change, the last value up to today's date is added. If there have been changes in values so that it is taken into account according to the date of change.
Thank you
Hi @FilipHanus ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
Test_1 =
var _currentvalue=
SUMX(FILTER(ALL('Table'),'Table'[OpportunityId]=EARLIER('Table'[OpportunityId])&&'Table'[Date]=EARLIER('Table'[Date])),[Value])
var _date=
MAXX(FILTER(ALL('Table'),'Table'[OpportunityId]=EARLIER('Table'[OpportunityId])&&'Table'[Date]<EARLIER('Table'[Date])),[Date])
var _nextvalue=
SUMX(FILTER(ALL('Table'),'Table'[OpportunityId]=EARLIER('Table'[OpportunityId])&&'Table'[Date]=_date),[Value])
return
IF(
_currentvalue<>_nextvalue,1,0)
Test_2 =
var _sum=
SUMX(
FILTER(ALL('Table'),'Table'[OpportunityId]=EARLIER('Table'[OpportunityId])&&'Table'[Date]<=EARLIER('Table'[Date])),[Test_1])
RETURN
IF(
[Test_1]=0,0,_sum)
Status =
CONCATENATEX(
FILTER(ALL('Table'),
'Table'[OpportunityId]=EARLIER('Table'[OpportunityId])&&[Test_2]>=2),
[Value],",")
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
I need a different result.
The right result I need is for every occasion, I need all the dates from the first change to today and the corresponding status. Based on when the change was made. A change is always valid until the next change date and for the last change until today's date.
I tried using this solution:
Create table:
Can someone advise me how to do it?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
78 | |
63 | |
52 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
61 | |
60 |