Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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])
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])
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.
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).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |