Has anyone ever faced a problem with As of Date calendar based on a Fiscal Year and tried to calculate MTD and QTD flags when the AsOfDate calendar is Weekly?
Basically, the problem stirs up that one cannot use the InMonthToDate and InQuarterToDate functions because what might be a week ending or week starting date in one year will not be the same in a prior or successive year. Therefore using these functions produces inaccurate flags.
I tried using FiscalWeekNumber hoping that would solve my problem but it too came short. The YTD and PYTD flags have no problem since I can use the FiscalWeekNumber. The full month flags have no problem since I can use the InMonth, InYear etc. functions.
The problem is really restricted to the MTD and QTD for prior or successive periods.
Attached is a QVW that explains the Problem.
The more I think, it seems that this may be an unsolvable problem. Anyone else have the same thoughts?
