I am trying to find the second latest date after the MAX date.
This is what I have tried but doesn't seem to work.
filter(orders, orders[orderdate]<> max(orders[orderdate])))```
Any idea on how I can return 2019-12-11?
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.
VAR MaxDate = CALCULATE(MAX('Table1'[Date]), ALL('Table1'[Date]))
VAR PreviousDate = CALCULATE(MAX('Table1'[Date]), 'Table1'[Date] < MaxDate)
return IF(ISBLANK(PreviousDate), MaxDate, PreviousDate)
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.
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
@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
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.
@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.
@Dee - You can do a measure like the following:
Second Largest = var _Largest = max(Orders[orderdate]) return CALCULATE( max(Orders[orderdate]), Orders[orderdate] < _Largest )
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])))
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.