Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

16 REPLIES 16
Dilushi_DB24
New Member

Do you know how to get the days difference between the last date and the second last date of a specific customer? Please can anyone assist?

 

 

Anonymous
Not applicable

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
Super User

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

 

Hi @amitchandak 
How to get last to last year from the database. Like if I have below data in the table, I want year 2018 & not 2019.

2021

2020

2018

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.