Calculate the maximum drop in my sales data

[marcgro]

Hello guys, 

I'm new to dax, and while I'm reading my ressource book there is a problem that I would like to solve that would help me immensely.

I have sales data for every one of my customers monthly over 5 years(With a date hierarchy set up). I would like to be able to calculate my maximum drop in sales for every customer. The drop should not be only a year to year drop. i.e. it would be the maximum value of the difference in sales between two of my years, assuming the lower sales year comes after the higher sales year.

i.e:

for customer 1:

Year	Sales
2015	20
2016	400
2017	180
2018	150
2019	160

Return: 250 (is 400 - 150)

In the same way:

for customer 2:

Year	Sales
2015	300
2016	20
2017	450
2018	220
2019	200

Return: 280 (300-20)

I hope you get the point.

One added layer of complexity is that since my sales data is monthly, it has to be aggregated to yearly before looking like the above tables.

Thanks in advance, all!

[v-juanli-msft · Accepted solution]

Hi @marcgro 

Create measures

sale_yearly = CALCULATE(SUM('Table'[sales]),FILTER(ALLSELECTED('Table'),'Table'[customer]=MAX('Table'[customer])),VALUES('date'[year]))

Measure = MINX(FILTER(ALLSELECTED('Table'),[year]>=MAX('Table'[year])),[sale_yearly])

Measure 2 = [sale_yearly]-[Measure]

Measure 3 = MAXX(ALLSELECTED('Table'),[Measure 2])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Assuming they are in the same table. You can have a year from the month.

Measure = 
var _a = SUMMARIZE('Table','Table'[customer],'Table'[Year],"_SUM",SUM('Table'[Sales]))
return
sumx(SUMMARIZE(_a,[customer],"_min",Minx(_a,[_sum]),"_max",Maxx(_a,[_sum])),[_max]-[_min])

Mark me @ for more info.

Appreciate your Kudos.

[marcgro]

Thanks for your quick reply. 

I am not getting the results that I want, because I think your formula misses out on the point that the low value should come at a later date than the high value (as I showed in the examples above).