Forum Discussion

Kolumam's avatar
Kolumam
Post Prodigy
8 years ago
Solved

Retrieving a value based on another column

I have two columns, one being Solar percentage and another is a date field. I want to retreive the value of the most recent date. how do I do this ?

Reports
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Kolumam,

     

    You try a measure like this:

     

    Measure =
VAR maxDate =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( Table1 ) )
RETURN
    CALCULATE ( MIN ( 'Table1'[Value] ), 'Table1'[Date] = maxDate )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Kolumam,

     

    You try a measure like this:

     

    Measure =
VAR maxDate =
    CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( Table1 ) )
RETURN
    CALCULATE ( MIN ( 'Table1'[Value] ), 'Table1'[Date] = maxDate )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

  • sydneycroud's avatar
    sydneycroud
    Frequent Visitor
    3 years ago

    Hi Dale, just trying to understand why you are using calculate (min in your code. Is that because that is the only way to bring back a single value, and you have to do it as a kind of pseudo calculation? I have a similar need where I have to return a value based on a date, and am thinking this will work for me. But it's not really a calculation, I am just saying on the latest date, what is the rating (column value), all in the same table. I guess this would work as I haven't seen a different solution.