topic Re: DAX measure (Max/Min) in DAX Commands and Tips

DAX measure (Max/Min)

Lygral63 — Fri, 27 Oct 2023 14:11:07 GMT

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

Re: DAX measure (Max/Min)

Dangar332 — Fri, 27 Oct 2023 14:23:12 GMT

Hi, Lygral63

try below code

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

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

Re: DAX measure (Max/Min)

Lygral63 — Fri, 27 Oct 2023 14:34:11 GMT

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.

Re: DAX measure (Max/Min)

FreemanZ — Mon, 30 Oct 2023 13:27:05 GMT

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:

Re: DAX measure (Max/Min)

Lygral63 — Tue, 31 Oct 2023 09:27:13 GMT

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.

Re: DAX measure (Max/Min)

Dangar332 — Tue, 31 Oct 2023 10:11:58 GMT

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

Re: DAX measure (Max/Min)

Lygral63 — Tue, 31 Oct 2023 11:34:49 GMT

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

Re: DAX measure (Max/Min)

FreemanZ — Tue, 31 Oct 2023 13:51:55 GMT

hi Lygral63 ,

the proposed measure still works, or?

Re: DAX measure (Max/Min)

Lygral63 — Tue, 31 Oct 2023 14:43:49 GMT

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