Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Last value with condition

Hi, I have a table and in that column, when I see 18, I need a new column to replace this value by the last value < 18. In that case, the 16 should be 16, the 18 should be 16 and the 27 should be 27...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    The first part gives us the last number < 18. The formula should be like below which just replaces 16 with "lastValue".

    Column = 
VAR lastValue =
    CALCULATE ( MAX ( [idSpecies] ), Table1[idSpecies] < EARLIER ( Table1[idSpecies] ) )
RETURN
    IF ( [idSpecies] = 18, lastValue, [idSpecies] )

    The example in the snapshot shows the last value of 10.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards,

    Dale