Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ytc-reports
Helper I
Helper I

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

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

15 REPLIES 15
ChrisPfP
Regular Visitor

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
Resident Rockstar
Resident Rockstar

Do u want a measure , 

 

pls try this

1.JPG

 

 

 

 

Baskar
Resident Rockstar
Resident Rockstar

Cool.

 

Create a new cal column ,look the image

1.JPG

 

 

 

 

Framet
Resolver II
Resolver II

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

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.

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

 

 

 

 

 

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

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

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?

ImagePBI.png

 

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

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!

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.

@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
Helper I
Helper I

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.