Measure for MAX date or LASTDATE

[DH_RF]

First time posting so please be nice.

I've tried several MAX date solutions but none seem to work.  In concept, it is the same as many posts on here with successful solutions.  I do not have access to Editor so this must done through a measure.

Listed is a table of Equipment that spans over several years.  Each time a Equipment Serial number's characteristics is changed through CRM, it creates another active row.  I'm looking for a general list of equipment by site, serial number, etc.  The only was to do this is to look at the last transaction date for each piece of equipment by equipment serial number.  So in theory, create a measure that will pull all the column data by looking at the MAX date or LASTDATE of each equipment serial number.  The most recent date has all of the correct column date.  Not every piece of equipment will be updated with new information every month else I could just list the most recent year and month.  One other kicker is the date column might not be formated as a date and it is not in the same "Equipment" dataset, but is is a 1 to 1 relationship.  I'm unable to change the format unless it is through a measure.  Tried, permissions restricted.

Here is my attempt #8.

Last Date =

CALCULATE(FIRSTNONBLANK(Equipment[Equipment Serial],1),

FILTER('Date','Date'[Date]= MAX('Date'[Date])))

Everything falls under Equipment except the Date.

Any help would be greatly appreciated.

DH RF

[DH_RF]

James,

Were you able to review my response?  Anyone able to help me solve the issue?  Else I just exported the data to Excel and filtered and sorted the data until I listed the most recent date record.

[v-jianpeng-msft]

Hi, @DH_RF 

Thank you very much @JamesFR06 . I think you can try the following DAX expression:

Last Date =
CALCULATE (
    MAX ( 'Date'[Date] ),
    ALLEXCEPT ( Equipment, Equipment[Equipment Serial] )
)

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DH_RF]

Unfortunately, when adding the measure, the system times out based on reached max resources.  Here is a link to the file:

HiLo Dashboards (5).pbix

The ouput is displaying only the most recent dated record located on the right for each equipment serial.

DH_RF

[v-jianpeng-msft]

Hi, @DH_RF 

I can't open the link you shared, you can share it via SharePoint? Please be careful not to include any private data.

Best Regards

Jianpeng Li

[JamesFR06]

HI

Sorry I have a problem with my account. Can you post the pbix via dropbox ?

[DH_RF]

Sorry, this is a corperate account so no Dropbox available and not able to download a free trial.  What about Google?

[JamesFR06]

Hi 

I forgot all on the Maxdate calulation so ==> var MaxDate=calculate(MAX('Date'[Date]),Equipment[Equipment Serial]=Eqserial,all(Equipement))

Try like this please 

[DH_RF]

Updated, no results (blank).  Regardless, I appreciate the help!

DH RF

[JamesFR06]

HI

Last Date =

var EqSerial=selectedvalue(Equipment[Equipment Serial])

var MaxDate=calculate(MAX('Date'[Date]),Equipment[Equipment Serial]=Eqserial

Var Datevalue=selectedvalue('Date'[Date])

Var result= if(MaxDate=Datevalue,1)

return

result

and after you put this measure on the filter panel and value = 1

and you should have only requiered records

[DH_RF]

No errors, but unfortunately nothing changed in the results.  The measure is in a general decimal format.  Does this need to change?  I would think not since the measure is returning a 1 (yes).

Thank you,

DH RF