Forum Discussion

astarkey's avatar
astarkey
Frequent Visitor
8 years ago
Solved

Return a Value based upon date in another column

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's avatar
    Zubair_Muhammad
    8 years ago

    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
        )
    )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      astarkey
      Frequent Visitor
      8 years ago

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

    • astarkey's avatar
      astarkey
      Frequent Visitor
      8 years ago

      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