You might want to tweak data model a bit on RateTable, to include one more column indicating end of RatePeriod, using that you can add one more condition to existing custom column to filter rows based on the Start and End of RatePeriod. something like below:
each RatePeriodEnd <= rper and RatePeriodStart <= rper)
I think I must be doing something wrong because the logic seems perfect to me, I've done as you suggested and created an [EndofRatePeriod] column. This is the last month the rate should be applied. The [RatePeriod] column should be the first month the rate should be applied.
Therefore, if the [InvoicePeriod] is less than or equal to [EndofRatePeriod] and greater than or equal to [RatePeriod], apply the rate.
See my screenshots showing what is happening:
THE RESULTMY RATE CUSTOM COLUMNTHE MAPPING TABLE WITH DATES