How to use DAX or PowerQuery to create a measure of trend?

[Anonymous]

Hi All,

If I have Sales per material for a couple of years, I want to know which materials are plummeting and which materials are skyrocketing.

I can write something as 
AVERAGE('Last Year Sales/Last Last Year Sales', 'Last Last Year Sales/Last Last Last Year Sales', 'Last Last Last Year Sales/Last Last Last Last Year Sales')

Personally believe it is not an effective way to catch trend, anyone has better idea on this?

---

Hi, sorry if I didn't made myself clear. 

If I have a table like this, how to create a column for sorting purpose to represent trend?

Product	Sales-May	Sales-Apr	Sales-Mar	Sales-Feb	Measure of Trend?
Apple	5	10	16	36	Dropping, should rank low
Orange	50	45	55	53	Stable, in the middle
Durian	20	15	17	9	Rising, should rank high

[Anonymous]

Hi @Anonymous 

So now your problem is solved? If yes, please provide your solution and mark it so that more people can see it. If it is not resolved, please follow what I said before and provide accurate data and criteria for judging sales trends so that we can continue to deal with the problem for you.

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous 

The data you provided makes me a little puzzled. For Apple, sales should be declining, but in your data ,it display rise ?

Could you provide accurate data and criteria for judging sales trends ? Under what circumstances are considered as rising and under what circumstances are considered as falling.

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous Thanks for pointing out, yes you are right.
Rectified the incorrect description...

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.

If you are looking for averages  of sum

averagex(values(Date[Year]), calculate(sum(Table[Values])))

rolling average

Rolling 2 = CALCULATE(averagex(values(Date[Year]), calculate(sum(Table[Values]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,Year))

Appreciate your Kudos.

[Anonymous]

Hi, thanks for answer albeit not quite answered the question, have append extra info in the description.