I have a table with these columns:
Item No, Date, and Price
I want to add a fourth column, Latest Price. Latest Price looks at all rows with the same Item No, finds the latest Date out of all those rows, and gives me the Price from that row.
Any idea what function I can use for Latest Price?
Thanks in advance!
Go to Solution.
Latest Price =
SUM ( 'Table1'[Price] ),
= CALCULATE (
MAX ( 'Table1'[Date] ),
FILTER ( Table1, 'Table1'[Item No] = EARLIER ( 'Table1'[Item No] ) )
&& [Item No] = EARLIER ( [Item No] )
In my case the table is just 'Table1' as I created on the fly.
This assumes no item has two prices on the same date. If that can happen you may need to use, max or avg to get a more meaningful value.
View solution in original post
So I've managed to make a column which calculates the MAX DATE, doing a calculate > max, allexcept (itemnumber) type function
So I have a column with the latest date. Any way I can use the latest date and the item number to look up the corresponding price?
Hi there,Depending on the format of your column called Date you could try:
Latest Price =
MAX ( 'MyTable'[Date] ),
'MyTable'[Item No] = EARLIER ( 'MyTable'[Item No] )
Let me know if this works for you.
Thanks for your help, but before I try it I'm a bit confused, as that function doesn't even reference the Price column. How could it output the latest price without the function being told to look at the price column?
Sorry, that should get you the latest date for each item. I'll come back in a second with the relevant price. I should have read it properly.
When I got to this part of the function,
EARLIER ( [Item No] )
It didn't like it. It gave me a red line saying the 'Earlier' context doesn't exist
Hmm, the formula I have provided is for a calculated column which means the row context is automatically created. Just a thought but are you sure you are adding this as a column and not a measure?
This is the forumula doing its thing on the test data you provided.
Create a new cal column ,look the image
Do u want a measure ,
pls try this
Kudos to you if you earned one of these! Check your inbox for a notification.
Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.
Find out where you can attend!