Forum Discussion

Dee's avatar
Dee
Icon for Helper III rankHelper III
6 years ago

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. ``` calcul...
Need Help
Tutorial Requests
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity Support
6 years ago

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.

 

Dee's avatar
Dee
Icon for Helper III rankHelper III
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    • Dee's avatar
      Dee
      Icon for Helper III rankHelper III
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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.