Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

return the second max date per month

Hi, I am trying to get the second max date per month. Please see below output.   Report Date         Second Max Date   5/1/2020                  5/20/2020    5/30/2020                5/20/2020  ...
Need Help
Show and Tell
az38's avatar
az38
Community Champion
6 years ago

Hi Anonymous 

try

Second Max Date = 
var _maxDate = CALCULATE(MAX('Table1'[Report Date]), ALLEXCEPT('Table1', 'Table1'[Report Date].[Month]) )

RETURN

CALCULATE(MAX('Table1'[Report Date]), ALLEXCEPT('Table1', 'Table1'[Report Date].[Month]), 'Table1'[Report Date] < _maxDate)