I have a data set that get new versions daily, this data set has four columns: version, date, basket and apples. Basically this tells us how many apples are, or will be, in a basket every single day, but whenever we get a new version we lose the data from the previous day version.
This is how the data would look when looking at the version from the 3th
| Date | data with no formula |
| 1st | |
| 2nd | |
| 3th | 1 |
| 4th | 3 |
| 5th | 6 |
this is how it would look when looking at a version from the 4th (future numbers can change as they update)
| Date | data with no formula |
| 1st | |
| 2nd | |
| 3th | |
| 4th | 3 |
| 5th | 7 |
Using this formula I was able to fill the blanks we have for previous dates on newer versions using data from previous versions. FYI: Versions are copied to another table to be used as reference.
SUMMARY_APPLES =
IF(
ISBLANK( MAX( SUMMARY_APPLES[# OF APPLES] ) ),
CALCULATE(
LASTNONBLANK( SUMMARY_APPLES[# OF APPLES], SUMMARY_APPLES[# OF APPLES]),
FILTER(
ALL( SUMMARY_VERSIONS ),
SUMMARY_VERSIONS[VERSION #] < MAX( SUMMARY_VERSIONS[VERSION #])
)
),
MAX( SUMMARY_APPLES[# OF APPLES] )
)
This is a comparison of how the data looks before and after the formula (when looking at a version from the 4th):
| Date | data with no formula | data with formula |
| 1st | | 2 |
| 2nd | | 3 |
| 3th | | 1 |
| 4th | 3 | 3 |
| 5th | 7 | 7 |
| Total | 10 | 10 |
As seen in the Total row, my problem arises when I try to get the total, as it only sums the originally non blank values. I know this happens because LASTNONBLANK does not work on an aggregation level, only on a row basis level, but is there any way to obtain the total sum INCLUDING all the values? (IE: total being 16 instead of 10)
