DAX measure (Max/Min)

[Lygral63]

DAX measures for Max and Min

I am looking for the best way to calculate a measure that will compare sales from multiple year in a given Calendar Week and returning the max value. The measure should return the max by Calendar Week based on total sales (eg. sum of countries). I would need the same kind of measure for Min. Any suggestions?

Table: Data
Country	Product	Year	Calendar Week	Sales
A	C	2015	1	100
A	C	2015	2	200
A	C	2016	1	150
A	C	2016	2	125
B	C	2015	1	200
B	C	2015	2	250
B	C	2016	1	100
B	C	2016	2	150
Calendar Week	Year	Total Sales
1	2015	300
1	2016	250
2	2015	450
2	2016	275
Output
Measure should return:
Calendar Week
1	300
2	450

[FreemanZ]

hi @Lygral63 ,

not sure if i fully get you, please try to plot a table visual with two measure like:

WeeklyMax = 
MAXX(
    VALUES(data[Year]),
    CALCULATE(SUM(data[Sales]))
)

WeeklyMin = 
MINX(
    VALUES(data[Year]),
    CALCULATE(SUM(data[Sales]))
)

it worked like:

[Lygral63]

I appreciate the effort, but I can't the the result I am expecting. The problem is that I have multiple years and need to max value from Calendar Week 1 (across all years), then the max from Calendar Week 2 (across all years) etc. So in total the measure should return the max value for every Calendar Week comparing data from all years.  I hope that makes it a bit more clear.

[Dangar332]

Hi, @Lygral63 

try below 

result =
calculate(max('table (2)'[total sales]),removefilters(''table (2)'[year]))

if not work then provide week1 and week 2 data

[Lygral63]

Country	Product	Year	Calendar Week	Sales
A	D	2015	1	100
A	D	2015	2	200
A	D	2015	3	150
A	D	2016	1	300
A	D	2016	2	200
A	D	2016	3	100
B	D	2015	1	200
B	D	2015	2	150
B	D	2015	3	450
B	D	2016	1	500
B	D	2016	2	300
B	D	2016	3	250
C	D	2015	1	125
C	D	2015	2	100
C	D	2015	3	150
C	D	2016	1	175
C	D	2016	2	200
C	D	2016	3	150
Calendar Week	2015	2016	Measure should return
1	425	975	975
2	475	700	700
3	750	500	750

[FreemanZ]

hi @Lygral63 ,

the proposed measure still works, or?  

[Lygral63 · Accepted solution]

Yes, it's works. Thanks a lot. Much more simple than I expected. Thanks.

[Dangar332]

Hi, @Lygral63 

try below code 

min sales by week = MIN('calender week'[Total Sales])

max sales by week = max('calender week'[Total Sales])

[Lygral63]

Thanks, but I don't think that works. I need a measure that looks at total sales by Year and by Calendar Week and then returns the max or min value. It could be that in Calendar Week 1 has the max value based on sales in 2016, but Calendar 2 has the max value based on sales in 2015. Hope it makes sense.