Forum Discussion
Incremental refresh messing up with query calculation
To make matters worse - Incremental Refresh partitions are based on regular calendar dates, not fiscal calendar dates.
We ended up introducing a fake "Partition Date" datetime column in our data to translate our fiscal periods into the desired partitions.
Can you clarify this "Partition Date" column? How do you direct incremental refresh to base the partition on dates in that column and not on the other date column?
Let's say our fiscal year goes from September to August. I want to refresh partitions by fiscal year. So I introduce a Partition Date that is shifted four months, so that the "2023" partition now actually contains data for FY2023 rather than calendar year 2023.
There is an interesting side effect to this - Standard Incremental Refresh can't handle future dates. You need to use XMLA for that, but you are already using it anyway when you are manually refreshing partitions.