Forum Discussion
Solved
Fill blank values with last not blank value based with calculated column
Hello!
I have a table with multiple products and duplicated dates where I need to show the last registered price. To give an example this is how my table looks like:
Date | Product | Price |
| 12-31-2023 | A | 1 |
| 12-31-2023 | B | 2 |
| 01-01-2024 | A | |
| 01-01-2024 | B | |
| 01-02-2024 | A | 3 |
| 01-02-2024 | B | 5 |
| 01-03-2024 | A | |
| 01-03-2024 | B | 6 |
And here is my desired result:
Date | Product | Price |
| 12-31-2023 | A | 1 |
| 12-31-2023 | B | 2 |
| 01-01-2024 | A | 1 |
| 01-01-2024 | B | 2 |
| 01-02-2024 | A | 3 |
| 01-02-2024 | B | 5 |
| 01-03-2024 | A | 3 |
| 01-03-2024 | B | 6 |
Thank you for any advice 🙂
output :
Column = var ds = TOPN(1, FILTER( 'Table', 'Table'[Product.1] = EARLIER('Table'[Product.1]) && not ISBLANK('Table'[Price]) && 'Table'[date]<=EARLIER('Table'[date]) ), 'Table'[date],DESC) return SELECTCOLUMNS(ds,'Table'[Price])let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
2 Replies
- Daniel29195
Community Champion
output :
Column = var ds = TOPN(1, FILTER( 'Table', 'Table'[Product.1] = EARLIER('Table'[Product.1]) && not ISBLANK('Table'[Price]) && 'Table'[date]<=EARLIER('Table'[date]) ), 'Table'[date],DESC) return SELECTCOLUMNS(ds,'Table'[Price])let me know if this helps .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠 - Greg_Deckler
Asmonk Try:
New Column = IF( [Price] <> BLANK(), [Price], VAR __Date = [Date] VAR __Product = [Product] VAR __Last = MAXX( FILTER( 'Table', [Product] = __Product && [Date] < __Date && [Price] <> BLANK() ), [Price] ) RETURN __Last )
