Forum Discussion
Incremental refresh messing up with query calculation
each [calday] >= Date.From(RangeStart) and [calday] < Date.From(RangeEnd)),
Flip this around to say
each DateTime.From([calday]) >= RangeStart and DateTime.From([calday]) < RangeEnd),
Unfortunately it's still the same...
I did a bit of additional troubleshooting and figured out the cause for the issue, I just need a help to figure out the solution now.
First, I realized that all of the "surpluss" dates appearing are always part of the calendar month that falls into the next fiscper - I hope an example below explains it.
While my incremental refresh was set to refresh 3 months - I had 12 "surpluss" dates.
I switched refresh to 90 days - 192 "surpluss" dates.
I switched to 1 Quarter - 0 "surpluss" dates (but the month I'm looking at is March, so no really mismatch between quarter per fiscper and quarter per regular calendar.
Based on this I assume that pbi service will take one by one whatever interval is selected in the incremental refresh (day, month, quarter, year), perform the calculation and append to previously calculated.
Ideally it would take one by one fiscper (not month or similar) but I understand that's not possible, so is there some way to be 100% sure that there will be no surpluss dates?
The only thing that comes to my mind is to refresh full dataset each day which kind of negates the point of incremental refresh.
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.