Working with Offset in Period Table Task

First, you may want to add a second Period Table Task to your SSIS package:

mceclip0.png

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).

mceclip1.png

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.

mceclip2.png

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.

mceclip3.png

This time, we will however need to tell the system that the financial calendar have been offset by e.g. three months.

mceclip4.png

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.

mceclip5.png

 

Was this article helpful?
0 out of 0 found this helpful

Comments

1 comment
  • 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.

    0

Please sign in to leave a comment.