How to Identify the most recent record - error

[Anonymous]

Hi all,

I've been at this for a while, and cannot seem to get it right!

I have a table of data that gets updated by users when there is an update to a job.

I am looking to find a way to identify the date of the most recent note per job number, then show the note relating to this date and job number.

I created two measures

MostRecentDate = MAX('Expediting notes'[Date of Note])

&

MostRecentDescription =
LOOKUPVALUE(

'Expediting notes'[Expediting Notes],
'Expediting notes'[Date of Note],
[MostRecentDate],
'Expediting notes'[EMIR Job number],
MAX('Expediting notes'[EMIR Job number]))

The first part works fine, and from testing it it brings back the max date.

The issue comes from the 2nd part:

Could this be that there are some instances where there are 2 or more updated notes from the same date stamp?

If so, what do you recommend that I do to overcome the issue?

Thanks,

Chris

[Icey]

Hi @Anonymous ,

Try this:

MostRecentDescription =
LOOKUPVALUE (
    'Expediting notes'[Expediting Notes],
    'Expediting notes'[Date of Note], [MostRecentDate],
    'Expediting notes'[EMIR Job number], MAX ( 'Expediting notes'[EMIR Job number] ),
    'Expediting notes'[Expediting Notes], MAX ( 'Expediting notes'[Expediting Notes] )
)

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[BA_Pete]

Hi @Anonymous ,

Any chance you could provide a small sample of your data please?

It sounds like it could be resolved in Power Query by creating a combined field of [Job Number] & [Date], for example, but need to see the data to ensure I understand the problem correctly.

Pete

[Anonymous]

Yeah, no worries:

There aren't too many columns - this is a simple sheet used to provide quick updates regarding job details.

I know the sample isnt too extensive, but it should give you an idea of the data contained.

Regarding a concat between job number and date of note - I dont think this would work, as the job number is constant right throughout its journey, but I'm definitely not an expert! 

Maybe I need to add a column into the data regarding what the number of that update is for that day.

This way, I would know that update number should be max

Thoughts?

[Ashish_Mathur]

Hi,

Share a simple dataset and show the expected result.

[Anonymous]

I opted to go back to my original source, removed the 1 record that had two entries for the same day (as they were duplicates), refreshed the dashboard and the measure worked.

I will just ensure that the source can only have one update per day.

[Anonymous]

In the data you can see that there are Job numbers in the left most column, followed by Job Name, the date of the note, and finally the note that has been left.

My goal is to identify the most recent note, and the date that the note was added to the list.

As mentioned in my post, it could be that there are some instances where there are two updated notes for the same job on the same day. As such I think this is what is causing the issue and resulting in the error.