Multi-Row Formula

[cpowers]

I have a set of Pricing Data, see attached for sample. Where I have a record for every price per the effective date. I want to know how long since the most recent effective date an asset has been that price. Which I accomplished using Multi-row formula. For example the price on 4/29/22 was 99.718 and it has been that price since 3/30/22. I want to be able to ignore all the records before 3/30/22 and do a summary like this (for all assets - this is just 1). I am not sure if I need another multi-row formula to indicate a Y/N of 'most recent price indicator' so I can ignore the rest and get my min/max date or if some other tool(s) would be better. I will be working with millions of rows so efficient processing would be preferred. Thanks in advance for any help/advice!

Desired Summary for Asset ID/Current Price

PRIMARY_ASSET_ID	SECURITY_PRICE_AMT	Days at Price	EFFECTIVE_DT	First_DT_of_Current _price
1234	99.718	21	4/29/2022	3/30/2022

[IraWatt]

Hey @cpowers,

From what I understand of the question you could just use a summarize tool and group on the Asset ID:

That should get you the details you want from each.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[cpowers]

@IraWatt thanks for responding. I should have been more clear in my post that although I've included a column in the sample data of "most recent Price" = Y/N - that is actually what I am struggling to create so that I can do the desired summary I included in my post/that you provided in your response. 

[IraWatt · Accepted solution]

Ah okay I have added that formula in:

I also Grouped by Asset ID so that it should apply separately to each ID. Let me know if it makes sense/works 😅 @cpowers 

[cpowers]

@IraWatt YYESSS, thanks you. I'm the worse at Multi-Row. I went back to my original data and the grouping on asset is key for the solution to work. Thanks again. I've accepted you response as the solution. 

[IraWatt]

No worries @cpowers 😆 great to hear it worked!