First, you may want to add a second Period Table Task to your SSIS package:
This Fiscal Period Table Task would then need to know about the offset, e.g. number of months, that this Fiscal calendar will be offset relatively to a normal January-December year.
E.g., if April is to become the first month of your Fiscal year, then you would need an offset of minus three months. Think of it as April normally being month number 4. With an offset of -3, April now becomes month number 1 (=4-3).
In the resulting FiscalPeriod table, notice how FiscalMonthNumbers in relation to FiscalMonthNames have been offset with minus three – e.g. March is now month number 12.
Also notice the FiscalYearName column. While the 24th March 2006 is in year 2006 by the normal calendar, it is actually still in year 2005 by the Fiscal calendar. Often, companies would like to see this information as Fiscal Year 2005/2006 to minimize potential confusion.
At this point, you would probably go to your SSAS project to:
- Add and relate the new table to the Data Source View.
- Create a new dimension, FiscalCalendar.
- Add the new dimension to the cube.
- Process the dimension and the cube.
Now, the Dynamic criteria definition, in the TARGIT Management Decorations, is initially not different from what we already know. Notice the Key references for years as well as for months.
This time, we will however need to tell the system that the financial calendar have been offset by e.g. three months.
In the TARGIT client, you will be able to see the effect on criteria involving years/fiscal years.
In TARGIT: Year to date (previous month) criteria on two different time dimensions: A normal calendar time dimension and a fiscal calendar time dimension.
Note: When working with "Year to date" or "Month to date" dynamic fiscal criteria, it is important that the "day" level contains day numbers - either "1, 2, 3 ... 30, 31" (D) or "01, 02, 03 ... 30, 31" (DD).
If the day level contains full dates like "2021-05-26", the dynamic fiscal criteria may fail.
Please sign in to leave a comment.