How to find minium date when the price started to decrease

[Natalia10000]

Hello,

I have the following problems, I have the table with prices and dates and I need to find minium date when the price started to decares (but the dates need to be in row) so e.g.

So the result should be 1/5/2024, because starting from the latest date, this is minium date when the price started to decrease.

In this case the result should be 1/7/2024

Could You please help?

[Rakesh1705]

Source Data

Change calculation

Latest Decreasing Date

Earliest Decreasing Date

if this solves your problem then please accept the same as your solution.

[FreemanZ]

hi @Natalia10000 ,

try like:

measure =

VAR _table =

ADDCOLUMNS(

     data,

     "change",

      [Price]-

      MAXX(FILTER(data, data[data]=EARLIER(data[date])-1, data[Price]

)

VAR _result = 

MINX(

    FILTER(

        _table,

        [change]<0

    ),

    data[date]

)

RETURN _result

[Natalia10000]

I would maybe add some more specifics to my problem, so this is the model:

Raw Data is a table with prices of materials on daily basis. Price should be shown on weekly basis, so this is just an average. What I need to find is the Year and Week (min one), with the price increase, but this min should be not from all of the data, but this should be min from the weeks that are subsequent, so the last week before the price starts to decrease, please see the example

In RM Check column I used the measure suggested so 

_RM Check =

VAR latest_aval = CALCULATE(MAX('Raw Data'[Attribute]))

VAR year_latest = CALCULATE(MAX('Date'[Year]),ALL('Date'),'Date'[Date]=latest_aval)

var week_latest = CALCULATE(MAX('Date'[WeekOfYear]),ALL('Date'),'Date'[Date]=latest_aval)

var yearweek_latest = CALCULATE(MAX('Date'[YearWeek]),ALL('Date'),'Date'[Date]=latest_aval)

var latest_price = CALCULATE([_RM Price],ALL('Date'),'Date'[Year]=year_latest,'Date'[WeekOfYear]=week_latest)

var price_table = CALCULATETABLE(

                        SUMMARIZE(

                            'Raw Data',

                            'Raw Data'[Index number],

                            'Date'[YearWeek],

                            'Date'[Date Index.Index],

                            "Price",CALCULATE([_RM Price])

                            ),

                        ALL('Date'))

VAR _table =

    ADDCOLUMNS(

        price_table,

        "change",

        [Price]-

        MAXX(FILTER(price_table, 'Date'[Date Index.Index]=EARLIER('Date'[Date Index.Index])-1), [Price])

        )

VAR _result =

    MINX(

        FILTER(

            _table,

            [change]<0

        ),

        'Date'[YearWeek]

    )

RETURN _result

but it gives incorrect result, could you please advice on this?

[Anonymous]

Hi @Natalia10000 ,

Do you want to get the min date in recent dates? Could you please provide some raw data in your tables 'Raw Data'(exclude sensitive data) with Text format, the formula of [_RM Price] and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards