Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

DAX expression

Hi,

I have data in the following format:

I need to find the max of month in each year, the expected output is like this:

Year Month max

2019 12

2020 5

How can i achieve this in DAX? Thank you

Hi Anonymous ,

You can create it as a measure like this:

Max Month in Year Measure = 
    CALCULATE( MAX('Date'[Month]),'Date'[Year] = SELECTEDVALUE('Date'[Year]))

Or you can create a column like this:

Max Month in Year = 
    CALCULATE( MAX('Date'[Month]), FILTER('Date', 'Date'[Year] = EARLIER('Date'[Year]) ))

4 Replies

Adescrit
Impactful Individual
4 years ago
Hi Anonymous ,

You can create it as a measure like this:

Max Month in Year Measure = CALCULATE( MAX('Date'[Month]),'Date'[Year] = SELECTEDVALUE('Date'[Year]))

Or you can create a column like this:

Max Month in Year = CALCULATE( MAX('Date'[Month]), FILTER('Date', 'Date'[Year] = EARLIER('Date'[Year]) ))
- Anonymous
  Not applicable
  4 years ago
  Hi Adescrit
  
  Thanks for your solution. Appreciate it.
Samarth_18
Community Champion
4 years ago
Hi Anonymous ,

You can drag your year column and month column then mark your month column summerized as Max

or create a measure like below:-

measure=max(table[month])

Thanks,

Samarth
- Anonymous
  Not applicable
  4 years ago
  Thanks for responding if I have tried creating a measure like this:
  measure=max(table[month])
  But the problem is, it needs to calculate that for each year. The above method just displays 12 for both the years. I need to calculate the above using DAX expression.

Related content