How To Get The Status Of All Opportunities By Months

[FilipHanus]

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 

[v-yangliu-msft]

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

[FilipHanus]

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]

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.

Can someone advise me how to do it?