Forum Discussion
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. There is another column with date.
Thanks
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
4 Replies
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/339586
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
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] ) ) RETURNThe formula IF([Column1] = 18, 16, [Colomun1]) get the same result?
Thanks
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
