2 Replies Latest reply on Feb 9, 2018 10:40 AM by Ty Stackhouse

# Finding most recent Price

I have a data source that contains a list of all of my items and their price effective dates along with the prices. I would like to be able to find the most recent price based on the date. I have been trying to use a Max(Effective Date) calculation along with an LoD calculation but have not found a solution. Essentially I would like to be able to see one row of item numbers along with latest date and price associated with that date.

I have attached a simplified workbook of the issue.

• ###### 1. Re: Finding most recent Price

Hi Ty,

So here is one way...

There are 2 LoDs which take the same basic form

[Last Date]

{FIXED [Item Number]: MAX

(

IIF({FIXED [Item Number]: MAX([Effective Date])}=[Effective Date],[Effective Date],NULL)

)

}

[Last Price]

{FIXED [Item Number]: MAX

(

IIF({FIXED [Item Number]: MAX([Effective Date])}=[Effective Date],[Price],NULL)

)

}

Alternatively (and simpler) is just to use this filter (set to True)

[Last Price Date]

{FIXED [Item Number]: MAX([Effective Date])} = [Effective Date]

The advantage of the first option is that we aren't filtering anything out, so you'd still have access to the other dates, if you wanted to make some sort of comparison.

hope that helps

• ###### 2. Re: Finding most recent Price

Perfect! I appreciate the help!