Forum Discussion
Get second earliest date in a column
Hi community,
I'd like to get the second earliest date in a column of date type. I'm trying the following formula but I'm still getting the earliest, not the second earliest.
second earliest date =
VAR mindate =
CALCULATE ( MIN ( 'Table'[column] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE (
MIN ( 'Table'[column] ),
FILTER ( ALL ( 'Table'), 'Table'[column] < mindate )
)
My formula returns 01/01/1000 instead of 01/09/2011
Any idea of what is wrong in my formula?
Thanks
- Try:second earliest date =VAR mindate =CALCULATE ( MIN ( 'Table'[column] ), ALLSELECTED ( 'Table' ) )RETURNCALCULATE (MIN ( 'Table'[column] ),FILTER ( ALLSELECTED ( 'Table'), 'Table'[column] > mindate ))
2 Replies
PaulDBrownCommunity Champion
Try:second earliest date =VAR mindate =CALCULATE ( MIN ( 'Table'[column] ), ALLSELECTED ( 'Table' ) )RETURNCALCULATE (MIN ( 'Table'[column] ),FILTER ( ALLSELECTED ( 'Table'), 'Table'[column] > mindate ))- amitchandak
Super User
Anonymous , Try like
second earliest date =
VAR mindate =
CALCULATE ( MIN ( 'Table'[column] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE (
MIN ( 'Table'[column] ),
FILTER ( ALL ( 'Table'), 'Table'[column] > mindate )
)or create a column rank and filter for 2
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
