Finding value related to latest Date

[ytc-reports]

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?

Thanks in advance!

[ChrisPfP]

I need to find a value based on a calculated date - ie what was [NewValue] on the first instance it was one of "Put it Right", "Stage 1" , "Stage 2" or "Stage 3". This is the measure I'm using to find the date but I can't work out how to show the [NewValue] (it's easy in SQL 🤣:

FirstStageDate =

var current_row_ParentId = min('History: Complaint'[ParentId])
var current_row_NewValue = min('History: Complaint'[NewValue])

var earliest_stage =
CALCULATE(
min('History: Complaint'[CreatedDate]),
FILTER(
ALLEXCEPT('History: Complaint','History: Complaint'[ParentId]),
'History: Complaint'[NewValue] = "Put it Right" || 'History: Complaint'[NewValue] = "Stage 1" || 'History: Complaint'[NewValue] = "Stage 2" || 'History: Complaint'[NewValue] = "Stage 3"
)
) return

earliest_stage

[Baskar]

Do u want a measure , 

pls try this

[Baskar]

Cool.

Create a new cal column ,look the image

[Framet]

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]

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]

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 · Accepted solution]

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

[AlexaderMilland]

What would you do if you have both a date and a time stamp you want to filter by?

[bvilten]

Hello @Framet ,

I appreciate you answer and it is quite helpful, but I need something slightly different. Instead of the latest price I need to pull the latest text input. Any ideas on how to handle this with text instead of numbers

ID, Action, Date

D9999 ,extension,12/09/2917

D9999 ,on board, 01/05/2015

D9999, off board, 01/01/2018

[ytc-reports]

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]

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.

[ytc-reports]

Oh yes, sorry, I did it as a measure. I tried it as a column and it looks perfect! I never know when to use columns or measures.

Anyway, I think you've solved it. Thanks buddy!

[Baskar]

Calculated Column :

 It will execute at Query loading time.

It will occupy the space in RAM , So it was costly.

It will give u better performance rather then Measure.

Calculated Measure :

It will Execute an visual load Time.

No need space.

If u have better DAX , it will give u better performance. 

Choose your way.

My personal suggestion :

1. If u have less data go with Column.

2. Have huge data go with Measure.

[Betsy]

@Baskar

Hi,

I'm trying to create a measure based on the solution you provided here, with a similar issue of the original poster., in that I want the latest, not the max on the latest date to be the result.

Am I correct in thinking the syntax for the measure you provided here is for the max price? How would I correct the following formula to give me just the latest?

Here's my example data:

Student ID; Package ID; sent_time

1; 1; 1/1/17

1; 2; 1/2/17

1; 4; 1/3/17

2; 2; 1/3/17

2; 4; 1/14/17

3; 1; 1/2/17

My two IDs are in text form. Want I want is a measure instead of a calculated column or table (I have two million rows and counting on this table, which has relationships with 4 other tables). So for this I would have Student ID 1, Package ID 4...Student ID2, Package ID 4, Student ID 3, Package ID 1 with only the Package IDs as the result.

I used your formula:

Stop ID = var cur_student_id = CONCATENATEX('Packages', 'Packages'[Student ID],",")
var max_date = CALCULATE(MAX('Packages'[send_time]),
FILTER(ALL('Packages'), 'Packages'[Student ID]=cur_student_id))

var result = CALCULATE(MAX('Packages'[Package ID]),
filter(ALL('Packages'), AND('Packages'[send_time]=max_date, 'Packages'[Student ID]=cur_student_id)))

return result

I don't get an error running the formula, but when I try to put the results in a report, it doesn't run through. I think the issue is this? var result = CALCULATE(MAX('Packages'[Package ID]), 

since I don't want the MAX Package ID, just what the Latest Package ID is for the latest date.

I hope I'm making sense!

Thanks for any help!

Betsy

[ytc-reports]

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?