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 

  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.

Need Help
Show and Tell

1 Reply

  • 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)