topic How to find minium date when the price started to decrease in DAX Commands and Tips

How to find minium date when the price started to decrease

Natalia10000 — Fri, 26 Jul 2024 14:00:36 GMT

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?

Re: How to find minium date when the price started to decrease

FreemanZ — Fri, 26 Jul 2024 14:51:10 GMT

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

Re: How to find minium date when the price started to decrease

Anonymous — Mon, 29 Jul 2024 06:26:15 GMT

Source Data

Change calculation

Latest Decreasing Date

Earliest Decreasing Date

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

Re: How to find minium date when the price started to decrease

Natalia10000 — Tue, 30 Jul 2024 08:01:37 GMT

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?

Re: How to find minium date when the price started to decrease

Anonymous — Thu, 01 Aug 2024 09:46:20 GMT

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