Last value with condition

[Anonymous]

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. There is another column with date.

Thanks

[v-jiascu-msft]

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]

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 · Accepted solution]

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

[Greg_Deckler]

So, what you want is the EARLIER function. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...