I am attempting to turn a table of prices over time into a pivot table that can be used to create reports. My issue is that per our process, if a price remains unchanged on a certain date, it is not added as a value to the table until the price changes. This ends up leaving blank gaps in my pivot table which causes issues with calculations.
So I am wondering if there is any way to have a pivot table fill in these blanks with the most recent known price until it reaches another non-blank value? When I looked into this I only found answers regarding filling in values from above into blank cells by special selecting blank cells and copying a cell ID with a value to all. This doesn’t seem to work with pivot tables and the way my table is set up will not allow for this.
Below are dummy examples of what I am trying to resolve with the blank cells I want to autofill boxed in red. So for example, I want to be able to fill in the price of avocado on 06/12/2019 with $0.95 without having to add that value to the data table. Thanks in advance for any help as this has really stumped me.