cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dee
Helper III
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
ET_Phone2
Resolver I
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)

It's a measure or a column ?

v-frfei-msft
Community Support
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 )
    )

Capture.PNG

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.

@Anonymous  you mean this pbix file?

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.

Date Only.PNG

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.

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?

I seem to not have the option for uploading.

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
)
amitchandak
Super User IV
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])))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors