And, it should select the 2nd most recent date regardless of the row location.

i.e. if the data table like this:

Product         Qty.        Date
Product A.      30.        5/12/2023
Product A.      40         1/11/2023
Product A.      15.        3/26/2023

It still needs to select: Product A.   3/26/2023

Hello @Dastan ,

I hope someone else in the community has a better answer that the one coming up as it is a bit convoluted, but it should lead you to what you are after.

1. You are going to create a RANK - information here - in the Dates to evaluate when is your 2nd most recent event.
   • The formula could be something akin of the following:

   • rank(Transaction list name.'Date',Transaction list name.'Product Dimension',asc,sequential)

2. Create a new dimension with two items manually input. Name them 1 and 2
3. Create a new property in the transactional list where you are going to use the ITEM function - information here - to map your Rank with the dimension you just created.
   • The formula could be something akin of the following:

   • item(text(Transactional list name.Rank), '1-2'.Name)

4. Build your target metric dimensioned by Product and formatted as Date.
5. Apply the following formula:

   • 'Transactional list name'.'Date'Dby first: 'Transactional list name'.'Product', 'Transactional list name'.'1-2']1filter: '1-2'='1-2'."2"].remove firstnonblank: '1-2']

That should resolve in the dates that you are after. I hope it makes sense and, as I said, that someone provides an easier answer.

Cheers!

Hi,

I might have something as well.

First, rank your dates in your transaction list using the Rank function

And then use the List.rank = 2  condition to isolate your rows:

This metric renders the quantities for the second most recent dates.

Hope this helps.

Best,

Thank you guys. It worked. But now, I have follow-up question:

How can I use ranking numbers to bring the subsequent row’s date value into the column next to the ranked column? Here:

So, I need to look previous ranked row and bring it’s date value into the current ranked row

Nevermind, actually I was able to do it with the use of Ranking Dimension, creating metrics, and using SELECT: Ranks-1

Hey @Dastan ,

Happy to read that it worked and you managed to resolve subsequent issues, good job!

Kind regards,

Bruno