topic Re: How to find Latest and 2nd Latest Read of each month? in DAX Commands and Tips

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

Anonymous — Wed, 26 Aug 2020 16:11:59 GMT

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

Previous Read =

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

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

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

Thank you.

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

amitchandak — Wed, 26 Aug 2020 16:26:31 GMT

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

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

Greg_Deckler — Wed, 26 Aug 2020 16:27:00 GMT

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