Have you ever considered incremental refresh for a large fact table but weren’t sure which date field to anchor it on?  A sales record, for instance, might only be considered “closed” and ready for archiving when it’s logged in the accounting books.  

The problem?  The AccountingDate field is only populated at that moment—until then, it’s blank.  And Power BI’s incremental refresh completely ignores records with blank dates.

The solution is surprisingly simple, and involves two steps:

1. When blank, assign a distinguishable future date to AccountingDate—say, 01 Jan 2099.  In your analytics, be sure to interpret this as “blank” to prevent unintended results.
   
2. Modify the incremental refresh range condition to include future dates (as I covered in this article).  Records with future dates will otherwise be ignored.

With this setup, sales records without an accounting date will remain in the latest partition until they receive a real AccountingDate.  At that point, they’ll shift to their final, fixed partition.

Gotchas to Watch Out For

• Relationships and Date Tables — If AccountingDate is used in relationships (e.g., with a Date table), ensure that 01 Jan 2099 doesn’t create unexpected results.  You may have to remove this date from the Date table and handle the resulting blanks in your measures, for example.
  
• Partition Size Growth — If the “latest partition” grows too large, consider additional maintenance strategies, such as using multiple future placeholder dates (e.g., rolling forward by year) to distribute records more evenly.  This will require a bit more logic added to the incremental refresh range condition.

How have you handled scenarios like this?  Let’s discuss!

Special thanks to my friend Raja Vuppala for inspiring this article.