Retrieving last month's value

BrigitteM
BrigitteM ✭✭✭
edited 01/11/23 in Formulas and Functions

Hi, I am trying to retrieve the previous months value based on a primary identifier.

I can set up my smartsheet to either move past values to another sheet or leave them all on a sheet.

The idea is when a user adds a new line item, with a primary identifier, the previous month's price can be automatically pulled into the row. the conditions im trying to put into a formula are


if primary identifier matches (using an @ row), pull value that was in the 'price' column for the most recent entry (based on 'as of date' column, it will be the last business day of the previous month) and put that value into the 'prior month, or most recent price' column


see screenshot of sheet setup below


Answers

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    edited 01/12/23

    I believe I'm following your situation, and if you add an auto-numbered column "Row ID", you should be able to use this formula:

    =INDEX(COLLECT(Price:Price, [Primary Identifier]:[Primary Identifier], [Primary Identifier]@row, [As of Date]:[As of Date], <DATE(YEAR([As of Date]@row), MONTH([As of Date]@row), 1)), COUNTIFS([Primary Identifier]:[Primary Identifier], [Primary Identifier]@row, [As of Date]:[As of Date], <DATE(YEAR([As of Date]@row), MONTH([As of Date]@row), 1)))

    It will collect the range of "Price" based on the "Primary Identifier", and only those that are earlier than the 1st of the "As of Date" month. The row identifier part of the INDEX function is then using the same logic, and will choose the nth row, before the 1st of the month within that collected range.

    The first field of each Primary Identifier will be an error as there is no previous Price associated with it. You can wrap that in an IFERROR to have it look cleaner if you'd like.

    And to clarify, this will retrieve the latest value prior to the first of the month. So it could be the previous month, or 5 months ago if none had been priced since.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!