cancel
Showing results for
Did you mean:
Helper III

How to find second latest date after max date

Hi all,

I am trying to find the second latest date after the MAX date.

orderdate
2019-12-11
2019-12-31
2019-12-31

This is what I have tried but doesn't seem to work.

``` calculate(max(orders[orderdate]),
filter(orders, orders[orderdate]<> max(orders[orderdate])))```

Any idea on how I can return 2019-12-11?

TIA

14 REPLIES 14
Resolver I

Have this been solved?

Am I right in thinking that you are looking for the previous date to the max date?

I had a similar issue and someone gave me the following formula. This will look for the previous date before your MAX date. If there is no date then the MAX date will be given.

Previous =

VAR MaxDate = CALCULATE(MAX('Table1'[Date]), ALL('Table1'[Date]))

VAR PreviousDate = CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate)

return IF(ISBLANK(PreviousDate), MaxDate, PreviousDate)

Regular Visitor

It's a measure or a column ?

Community Support

Hi @Dee ,

Please create a measure as below.

``````Measure =
VAR maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] < maxdate )
)
``````

For more details, please check the pbix as attached.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper III

@Anonymous  you mean this pbix file?

Helper III

I tried all the mentioned solutions but I still get the same date different from the real  second date.

Anonymous
Not applicable

@Dee  - Is the column a Date/Time column? If so, this could be problematic. You can convert it to a date column in Power Query.

Helper III

I understand now, this only brings out one date, I should have phrased my question better, supposedly I have a column with different dates for different users how do I return second dates for them all?

The formula is actually correct for getting the max date(for anyone looking for just one) how do I get it in a column

Anonymous
Not applicable

@Dee - If you create a Table Visual, and add People and this Measure, it should calculate the Measure for each People. The idea is that each Person is a filter which limits the data, and the Measure is calculated within that context.

Helper III

Hi, @Anonymous thank you for the response, I've actually tried but it ends up filling in the same date for all,

let me explain a bit more
supposedly I have

A 3.2.2019

A.2.2.2019

B.4.2.2019

B.1.2.2019

C.5.2.2019

C2.2.2019

For max of each, I would get 3.2.2019,4.2.2019,5.2.2019

How do I bring the second one now out individually for each, cause what the formula I have, is returning the max of them all which is 5.2.2019 and returning 4.2.2019 as the second max, not sure if I have explained exactly what I need?

I would be grateful for your help.

Anonymous
Not applicable

@Dee  - Could you share our pbix file?

Helper III

Anonymous
Not applicable

@Dee  - You'll need to upload it to OneDrive / Dropbox or some other file sharing site and then share the link. Careful not to include any sensitive data.

Anonymous
Not applicable

@Dee - You can do a measure like the following:

``````Second Largest =
var _Largest = max(Orders[orderdate])
return CALCULATE(
max(Orders[orderdate]),
Orders[orderdate] < _Largest
)``````
Super User IV

try putting all

``````calculate(max(orders[orderdate]),
filter(all(orders), orders[orderdate]<> max(orders[orderdate])))
or
calculate(max(orders[orderdate]),all(orders[orderdate]),
filter(orders, orders[orderdate]<> max(orders[orderdate])))``````

Proud to be a Super User!

Announcements