The Date Table is common across all Fenwick Gold for Power BI apps and supports three calendars:
- Fiscal Calendar: 12 month calendar starting on the 1st of any month other than January.
- Standard Calendar: 12 month calendar starting on the 1st of January.
- Weekly Calendar: Non-12 month calendar. Generally used for reporting on 445, 454, or 544 periods.
NOTE: Please ensure that you have designated a Calendar Type on the Fenwick Gold Setup for Power BI page in Business Central. To ensure that the reports represent your data accurately, please use date fields from the calendar that matches your designated Calendar Type in Business Central.
Common Fields
These fields that are common across all three calendars.
| Field Name | Description |
|---|---|
| Date | The date table key. Specifies the unique value of a specific date record. |
| Week Day | This field shows the day of the week, displayed as the first three letters of the day, e.g. Mon, Tue, Wed, etc. |
| Day Type | This field indicates whether the day of the week is a working day or non-working day. |
| Relative Day | Specifies the position of the date record relative to the Today reference. The Today reference is calculated each time the dataset is refreshed. The calculation is based on the UTC Offset value set on the Fenwick Gold Setup for Power BI page. |
Date Periods
| Field Name | Description |
|---|---|
| Year | This field displays the whole number of the year, in YYYY format (e.g. 2023). |
| Quarter | Specifies the quarter of the year in which the record falls. |
| Quarter Year | This field displays the quarter and year of the record, such as Q1 2023. |
| Month | This field displays the month of the entry. |
| Month Year | This field displays the month and year of the entry, for instance, “July 2023”. |
| Week | This field displays the number of the week within the year of the record. |
| Week Year | This field displays the week number and year of the record, for example, ‘W02 2023’ |
Relative Fields
These fields are designed to provide information about the position of the week, year, month, or quarter, relative to the Today reference. Values can be either negative or positive.
| Field Name | Description |
|---|---|
| RelativeWeekPos | This field shows the difference in weeks between the current week and the week of the record, indicating how many weeks the entry is behind or ahead of the current week. |
| RelativeMonthPos | This field indicates the difference in months between the current month and the month of the record, showing how many months the entry is behind or ahead of the current month. |
| RelativeQuarterPos | This field displays the difference in quarters between the current quarter and the quarter of the record, indicating how many quarters the entry is behind or ahead of the current quarter. |
| RelativeYearPos | This field displays the difference in years between the current year and the year of the record, indicating how many years the entry is behind or ahead of the current year. |
Start/End Date Fields
| Field Name | Description |
|---|---|
| StartOfMonth | This field represents the start date of the month, i.e., the first day of the month in the calendar. |
| EndOfMonth | This field represents the end date of the month, i.e., the last day of the month in the calendar. |
| StartOfQuarter | This field represents the start date of the quarter, i.e., the first day of the quarter in the calendar. |
| EndOfQuarter | This field represents the end date of the quarter, i.e., the last day of the quarter in the calendar. |
| StartOfYear | This field represents the start date of the year, i.e., the firstday of the year in the calendar. |
| EndOfYear | This field represents the end date of the year, i.e., the last day of the year in the calendar. |
Number of Day Fields
| Field Name | Description |
|---|---|
| MonthDays | Number of Days in the month. |
| QuarterDays | Number of Days in the quarter. |
| YearDays | Number of Days in the year. |
Sequential Day Fields
These fields are only common to the Standard and Fiscal calendar.
| Field Name | Description |
|---|---|
| DayOfMonthNumber | Sequential number of the day in the month. |
| DayOfQuarterNumber | Sequential number of the day in the quarter. |
| DayOfYearNumber | Sequential number of the day in the year. |
Previous Date Fields
| Field Name | Description |
|---|---|
| DatePreviousWeek | Same relative day in previous week. |
| DatePreviousMonth | Same relative day in previous month. |
| DatePreviousQuarter | Same relative day in previous quarter. |
| DatePreviousYear | Same relative day in previous year. |
Weekly Calendar
These fields are specific to the Weekly calendar.
| Field Name | Description |
|---|---|
| Period | A 4-week period. Ranges from 1 to 13 with an additional 14th period for any year with 53 weeks. |
| StartOfWeek | First Day of week. |
| EndOfWeek | Last Day of week. |