BTW - @Keith is spot on that none of this will work properly w/o a valid date table marked as such. I strongly recommend using @Melissa’s awesome Extended Date Table.
Also, here’s a video that @sam.mckay did on slowly changing price dimensions that explores another way to solve this problem through disconnected tables and the use of TREATAS to create a virtual relationship to the fact table:
- Brian