Dynamically Calculating Fiscal Month and Year

[#1]

I have two dates fields, a list in an AcctSetup table and a list in a Bill Table:

1. FiscalYearBeginDate
   for example 7/1/2012, 8/1/2012, 9/1/2012, etc. (AcctSetup Table)
2. BillStartDate for
   example 2/1/2013 (Bill Table)

I load my AcctSetup Table:

   AcctNum

   FiscalYearBeginDate

I load my Bill Table

   AcctNum

   BillStartDate

What I need to know is the Fiscal Month and Fiscal Year for the BillStartDate. I'll probably put this in a Load Script to perhaps add those two additional fields to the Bill Table:

If FiscalYearBeginDate = 7/1/2012 then BillStartDate 2/1/2013 is Month 7 of Fiscal Year 2012, BillStartDate 8/1/2012 is Month 6 of Fiscal Year 2012, 9/1/2012 is Month 5 of Fiscal Year 2012, etc.  So for each BillStartDate I need to know which month and fiscal year that date falls into.

Any suggestions on what the calcs should be in the load script for this?

[Gysbert_Wassenaar]

See this blog post: Fiscal Year

Also see this discussion for the creation of a master calendar: Tutorial - Using Common Date Dimensions and Shared Calendars. The qvc library used in the tutorial can create fiscal calendars starting at any month you like.