Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
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 ?
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.
@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.
@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.
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
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.
I seem to not have the option for uploading.
@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])))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |