dax query

[Arjun_reddy]

Hi Community team,

need you help on one of my requirement:
can you please write a dax measure on the below formula 
formula=((max year of total occasions on highest retail channel)-(same period last year of total occasions on retail channel))/(same period last year of total occasions on retail channel)
note: which is havin highest retail channel on max year for the same retail channel should reflect for pevious year 

category	year	sales		Row Labels	bulbs	chairs	sofa	tables
chairs	2021	5000		2021	4000	13800	5500	12000
tables	2021	6000		2022	8000	8000	9200	3400
bulbs	2022	6000
sofa	2022	4500			sofa is the highest value in max year the value is: 9200 and the same category sofa get the value on previous year sales
chairs	2022	8000
chairs	2021	6000		growth rate:	67%
chairs	2021	2800		sample formula=( (max year of highest top1 category on sales)-(previous year of same category on sales))/(previous year of same category on sales)
sofa	2022	4700
bulbs	2022	2000
sofa	2021	5500
tables	2021	6000
tables	2022	3400
bulbs	2021	4000

Thanks,

Arjun Reddy. L

[Arjun_reddy]

Hi ryan,

I am getting the value format error can you please correct the below query, and where i can add the power bi file into this post, can you please help me.

growthrate_on_occ =

VAR  _year=max('Tostitos_MP'[YEAR])

VAR _t=TOPN(1,SUMMARIZE(FILTER('Tostitos_MP','Tostitos_MP'[YEAR]=_year),'Tostitos_MP'[RETAIL CHANNEL],"sale",sum('Tostitos_MP'[TOTAL OCCASIONS])),[sale] )

var _C= maxx(_t,[RETAIL CHANNEL])

VAR _cy=sumx(FILTER('Tostitos_MP','Tostitos_MP'[YEAR]=_year&&'Tostitos_MP'[RETAIL CHANNEL]=_C),'Tostitos_MP'[TOTAL OCCASIONS])

VAR _py=sumx(FILTER('Tostitos_MP','Tostitos_MP'[YEAR]=(_year-1)&&'Tostitos_MP'[RETAIL CHANNEL]=_C),'Tostitos_MP'[TOTAL OCCASIONS])

return _cy

Thanks, 

Arjun Reddy

[ryan_mayu · Accepted solution]

maybe try to convert year column from TEXT to number

[Arjun_reddy]

after changed the year format as value, the dax query working fine and getting the accurate value,

Thanks a lot team, your helping is really awesome 

Arjun Reddy.

[ryan_mayu]

you are welcome

[Ashish_Mathur]

Hi,

What if more than 1 category has the highest sale in the farthest year. say sofa and bulbs?  How should the growth rate be calculated then?

[ryan_mayu]

@Arjun_reddy 

is this what you want?

Measure = 
VAR  _year=max('Table'[year])
VAR _t=TOPN(1,SUMMARIZE(FILTER('Table','Table'[year]=_year),'Table'[category],"sale",sum('Table'[sales])),[sale])
var _C= maxx(_t,[category])
VAR _cy=sumx(FILTER('Table','Table'[year]=_year&&'Table'[category]=_C),'Table'[sales])
VAR _py=sumx(FILTER('Table','Table'[year]=(_year-1)&&'Table'[category]=_C),'Table'[sales])
return _cy/_py-1

pls see the attachment below