Return a Value based upon date in another column

[astarkey]

This seems like it would be straight forward but I can't seem to put my finger on a solution....

I have similar to this in a table...

Equip ID      Meter Reading     Date Of Reading

abc123            50                            01/01/2017

def 456            70                             01/02/2017

abc123             40                             10/01/2017

abc 123            80                            01/07/2017

I want to return the first an last meter readings fro equip ID abc 123 based upon date of reading... (not min and max of reading)

However what I'm doing would return 40 - 01/01/2017 & 80 - 01/07/2017 which is wrong!!

It should be........ 50 - 01/01/2017 & 80 - 01/07/2017

can I achieve this with a measure? and how!

[Zubair_Muhammad · Accepted solution]

@astarkey

Try this MEASURE

LastMeterReading =
VAR last_date =
    MAX ( TableName[ Date Of Reading] )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableName[Meter Reading   ], 1 ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[Equip ID] ),
            TableName[ Date Of Reading] = last_date
        )
    )

[astarkey]

Thanks @Zubair_Muhammad on second look today this did actually return the result I was after, Many Thanks!!

[astarkey]

thanks @Zubair_Muhammad but that didnt seem to work, its possibly over enginneered...

This measure will go into a matrix table and therefore I dont think I need to reference the equip ID in the  measure with the equip ID being the detail in the row (certainly that seems to work for other measures I have in there)

This feelis like it should be easy! and the last piece of a very complex jigsaw I have, any other ideas are very welcome! 

Thanks

[Zubair_Muhammad]

HI @astarkey

Could you show a screen shot of your Matrix Table or Final OutPut?

[astarkey]

Top image hopefully shows you my problem the bottom matrix returns the correct first and last date but not the first and last meter reading, it returns the min and max meter reading..... the small matrix at the top also shows this issues, the top matrix has the meter readings in the correct order, but I only want the first and last readings in the main matrix that will be exported to show the hours use between first and last reading. 

The second image is an example of the data, my data is a series of job numbers that are associated to the equip id's I'm trying to analyse, the columns I'm using for the meter reading analysis is ECclockhours and ECtimestamp, I cant really edit the query becuase I'm pulling other parts of the data into the mtrix for other elements of the analysis

Hope this makes sense! your help is greatly appreciated