How to find Latest and 2nd Latest Read of each month?

[Anonymous]

Hi,

Here is my data sample,

I want to create a table that looks like this

I was able to get the month and the Last Read, but was not able to get the 2nd last

Here is the DAX that I have used:

1)
Last date = MAX('Consumption (2)'[Adjusted Read Date])

2)

Previous Read =

CALCULATE(MAX('Consumption (2)'[Read]),
FILTER(

ALL('Consumption (2)'[Adjusted Read Date]),

'Consumption (2)'[Adjusted Read Date] < [Last date]))

Thank you.

[Greg_Deckler]

@Anonymous - Maybe:

Latest =
  VAR __LatestDate = MAXX('Table',[Date])
RETURN
  MAXX(FILTER('Table',[Date]=__LatestDate),[Read])

2nd Latest =
  VAR __LatestDate = MAXX('Table',[Date])
  VAR __2ndLatest = MAXX(FILTER('Table',[Date]<>__LatestDate),[Date])
RETURN
  MAXX(FILTER('Table',[Date]=__2ndLatest),[Read])

Basically variations of Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[amitchandak]

@Anonymous , one way is to create  rank column on date inside moth and use that

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

or try these measures

last value = CALCULATE(lastnonblankvalue(Table[Date],max(Table[Read])),DATESMTD('Date'[Date]))

second last value =
var _max = CALCULATE(max(Table[Date]),DATESMTD('Date'[Date]))
return
CALCULATE(lastnonblankvalue(Table[Date],max(Table[Read])),DATESMTD('Date'[Date]), filter(Date, Date[Date]<_max))

or
second last value =
var _max = CALCULATE(max(Table[Date]),DATESMTD('Date'[Date]))
return
CALCULATE(lastnonblankvalue(Table[Date],max(Table[Read])),DATESMTD('Date'[Date]), filter(Table, Table[Date]<_max))