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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
FilipHanus
Helper I
Helper I

How To Get The Status Of All Opportunities By Months

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. 

FilipHanus_0-1714398447809.png

 

So, this one is changed at 1/6/2023 and until today's date is 100 because it has not been changed.

FilipHanus_1-1714398724045.png

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

FilipHanus_2-1714398823077.png

 

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 

 

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @FilipHanus ,

 

I created some data:

vyangliumsft_0-1714444998475.png

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:

vyangliumsft_1-1714444998477.png

 

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.

 

FilipHanus
Helper I
Helper I

I tried using this solution: 

 

Create table: 

DateTable =
SELECTCOLUMNS(
    GENERATE(VALUES('Historie příležitosti'[OpportunityId]),GENERATESERIES(MIN('Historie příležitosti'[Date]),TODAY())),
    "orderNo",[OpportunityId],"startDate",[Value]
    )
 
And create column: 
_Status =
VAR _a =
    CALCULATE (
        MAX ('Historie příležitosti'[Value]),
        FILTER ('Historie příležitosti','Historie příležitosti'[Date] <= EARLIER ( 'DateTable'[startDate] )&& 'Historie příležitosti'[OpportunityId]= EARLIER ( 'DateTable'[orderNo] ))
    )
RETURN
    // _a
    IF (
        CALCULATE (DISTINCTCOUNT ( 'Historie příležitosti'[Value]),FILTER ( 'Historie příležitosti', 'Historie příležitosti'[OpportunityId] = EARLIER ( 'DateTable'[orderNo] ) )) <= 1,
        _a,
        IF (
            [startDate] <= CALCULATE (MAX ( 'Historie příležitosti'[Date]),FILTER ( 'Historie příležitosti', [OpportunityId] = EARLIER ( 'DateTable'[orderNo] ) ))
            &&
            [startDate] >= CALCULATE (MIN ( 'Historie příležitosti'[Date]),FILTER ( 'Historie příležitosti', [OpportunityId] = EARLIER ( 'DateTable'[orderNo] ) )),
            _a,
            IF([startDate] <= CALCULATE (MAX ( 'Historie příležitosti'[Date]),FILTER ( 'Historie příležitosti', [OpportunityId] = EARLIER ( 'DateTable'[orderNo] ) )),
            _a)
        )
    )
But this solution doesn't work for me. 1) It takes the first date of absolutely everyone for every occasion. I would always need a first date for the given occasion 2) Value always takes the maximum for that given occasion, but it can happen that a larger value changes to a smaller one and it should show the smaller value.
 
This can be seen here, where from 1/1/2024 all values are 90 even though they should be 0 from 2/1/2024. Value changes can be seen here.FilipHanus_1-1714400371665.pngFilipHanus_2-1714400381020.png

 

Can someone advise me how to do it?

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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