Hi all,
I have products and they have different historical prices and I only want to display the price based on the latest price date. Does anyone know how to do this please. I’ve tried using max on price date but that doesn’t work. Even with a max on the price date it’s still including an older price that I don’t want to show. The selling price that I want to display in the example is 106 because its price date is the most recent.
Does anyone know a method for this please that will work across all the different products?
You need a variable to find the maximum price date within each p-code. I called mine Var Max Price Date…
=Max([Price Date]) In ([p-code])
That variable works by modifying the default calculation context. I refer to this blog post often on this topic.
And a second variable to determine if a particular row’s Price Date is the maximum. I called mine Is Max Price Date…
=If([Price Date] = [Var Max Price Date]; 1; 0)
I added some extra data to make a more comprehensive example…
You can then add a filter to only show the values where Is Max Price Date = 1.
And you can delete those variable columns from your table if you do not want them there.
That’s it.
P.S. I used this technique to recreate and add to your sample data.
1 Like
It’s not ideal, but try this:
Latest Date = max([Price Date])in([p-code])
Last price = if([Latest Date]=[Price Date])then([WSP5])
Current Price = max([Last price])in([p-code])
Being able to use that price in other calculations is difficult, but if all you want to do is display and export it, it should be fine.
Thank you, that worked!