cancel
Showing results for
Did you mean:
Regular Visitor

## Finding value related to latest Date

Hi guys.

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.

 Item No Date Price Latest Price 12345 Jan 1 4.33 4.20 12345 Jan 3 4.20 4.20 12345 Jan 2 4.58 4.20 55555 Jan 1 102.5 111.1 55555 Jan 3 111.1 111.1

Any idea what function I can use for Latest Price?

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Finding value related to latest Date

Hi,

```Latest Price =
CALCULATE (
SUM ( 'Table1'[Price] ),
FILTER (
'Table1',
'Table1'[Date]
= 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.

Any better?

Cheers

Thomas

13 REPLIES 13
Regular Visitor

## Re: Finding value related to latest Date

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?

Regular Visitor

## Re: Finding value related to latest Date

Hi there,

Depending on the format of your column called Date you could try:

```Latest Price =
CALCULATE (
MAX ( 'MyTable'[Date] ),
'MyTable'[Item No] = EARLIER ( 'MyTable'[Item No] )
)```

Let me know if this works for you.

Cheers

Thomas

Regular Visitor

## Re: Finding value related to latest Date

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?

Regular Visitor

## Re: Finding value related to latest Date

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.

Regular Visitor

## Re: Finding value related to latest Date

Hi,

```Latest Price =
CALCULATE (
SUM ( 'Table1'[Price] ),
FILTER (
'Table1',
'Table1'[Date]
= 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.

Any better?

Cheers

Thomas

Regular Visitor

## Re: Finding value related to latest Date

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

Regular Visitor

## Re: Finding value related to latest Date

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.

Super Contributor

## Re: Finding value related to latest Date

Cool.

Create a new cal column ,look the image

Highlighted
Super Contributor

## Re: Finding value related to latest Date

Do u want a measure ,

pls try this

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,265)