Forum Discussion
Return a Value based upon date in another column
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 ) )
Community Champion
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
)
)
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
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