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 CountryProductYearCalendar Week Sales AC20151100 AC20152200 AC20161150 AC20162125 BC20151200 BC20152250 BC20161100 BC20162150 Calendar WeekYearTotal Sales 12015300 12016250 22015450 22016275 Output Measure should return: Calendar Week 1300 2450

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

DAX measure (Max/Min) | Microsoft Fabric Community

8 Replies

Dangar332
Resident Rockstar
2 years ago
Hi, Lygral63

try below code

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

max sales by week = max('calender week'[Total Sales])
- Lygral63
  Helper I
  2 years ago
  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.
FreemanZ
Super User
2 years ago
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 - Weekly Min Max of multiple years.pbix32 KB
- Lygral63
  Helper I
  2 years ago
  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
    Resident Rockstar
    2 years ago
    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

Forum Discussion

DAX measure (Max/Min)

8 Replies

Related content

Total Min/Max measure where and keep the value static

Min Max Tooltip & future months

DAX Measure

Min / Max Price

write measure/dax to calculate min max and % difference

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