cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ytc-reports Regular Visitor
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 NoDatePriceLatest Price
12345Jan 14.334.20
12345Jan 34.204.20
12345Jan 24.584.20
55555Jan 1102.5111.1
55555Jan 3111.1111.1

 

Any idea what function I can use for Latest Price?

 

Thanks in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Framet Regular Visitor
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

 

 

 

 

 

View solution in original post

13 REPLIES 13
ytc-reports Regular Visitor
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?

Framet Regular Visitor
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

ytc-reports Regular Visitor
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?

Framet Regular Visitor
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.

Framet Regular Visitor
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

 

 

 

 

 

View solution in original post

ytc-reports Regular Visitor
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

Framet Regular Visitor
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?

ImagePBI.png

 

This is the forumula doing its thing on the test data you provided.

Baskar Super Contributor
Super Contributor

Re: Finding value related to latest Date

Cool.

 

Create a new cal column ,look the image

1.JPG

 

 

 

 

Highlighted
Baskar Super Contributor
Super Contributor

Re: Finding value related to latest Date

Do u want a measure , 

 

pls try this

1.JPG

 

 

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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