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

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

Hi Anonymous,

 

Try this formula as a calculated column.

Column =
VAR lastValue =
    CALCULATE ( MAX ( [Column1] ), Table3[Column1] < EARLIER ( Table3[Column1] ) )
RETURN
    IF ( [Column1] = 18, 16, [Column1] )

Best Regards,

Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi v-jiascu-msft,

Two things about that:

 

1. It works for now but sooner or later the number in that column will change, except the 18. So when I'll see the 10, I'll want the 18 become 10. The 18 should always become the last number < 18.

 

2. In that formula I don't understand the reason of the first part: 

VAR lastValue =
    CALCULATE ( MAX ( [Column1] ), Table3[Column1] < EARLIER ( Table3[Column1] ) )
RETURN

 The formula IF([Column1] = 18, 16, [Colomun1]) get the same result?

Thanks

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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