Highest and lowest Category from last 12 months

[heygowtam]

I have a Table of three columns for the last 3 years, [Sale][Date][Category]

I am looking for a DAX which picks up the best(Highest) performer and worst (Least) performer by Category based on Sales for the Last 12 months

If I use Card on Visualisation part, I should be able to show in Two Cards (Best and lowest PErformer )

[amitchandak · Accepted solution]

@heygowtam , First create a rolling 12 month measure

Rolling 12 = CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

then use TOPN to get

TOP1 Category = CALCULATE(max('Sales'[Category]) TOPN(1,ALLSELECTED('Sales'[Category]), [Rolling 12 ],DESC), values('Sales'[Category]))

Bottom Category = CALCULATE(max('Sales'[Category]) TOPN(1,ALLSELECTED('Sales'[Category]), [Rolling 12 ],asc ), values('Sales'[Category]))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

TOPN: https://youtu.be/QIVEFp-QiOk

[heygowtam]

perferct, jsut there is a comma missing before TOPN. Thats all