return the second max date per month

[Anonymous]

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 

  5/20/2020                5/20/2020 

  6/30/2020                6/15/2020

  6/15/2020                6/15/2020 

  6/01/2020                6/15/2020 

My current measure is

Second Max Date = CALCULATE(MAX('Table1'[Report Date]), FILTER('Table1', 'Table1'[Report Date]<>MAX('Table1'[Report Date])))

However, it returns to me this output

Report Date         Second Max Date

  5/1/2020                  6/15/2020 

  5/30/2020                6/15/2020 

  5/20/2020                6/15/2020 

  6/30/2020                6/15/2020

  6/15/2020                6/15/2020 

  6/01/2020                6/15/2020 

Please advise how can i get the correct output. 

Thanks.

[az38]

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)