Analytical Need
Customer's fiscal year does not comply with the Georgian 12-month calendar and starts on a different date than January 1st.
Example:
Customer’s fiscal year is from April 1st to March 31.
This means that April should be flagged as the first month of the year, May as the second, and so on.
Modeling Challenge
All date functions (such as YTD / MTD / QTD formulas, change over time, and all functions under the dashboard's Quick Functions feature) operate under the assumption that a year starts on January 1st.
Solution
We will create another field in out dim date which will calculate the new fiscal date for each existing date.
SQL:
Assumption - the fiscal year starts on April 1st. In this case, we will need to set April as the first month of the year. This we will do by subtracting 3 months from each date. In case the fiscal year doesn't start in April, simply replace the 3 with the appropriate number.
Create converted date field by subtracting 3 months to every date:
1 addmonths([Date], -3) as [Fiscal Converted Date]
- Set field type to be Date-Time (in case this is in a new field in a source table)
In the dashboard, use the new field (Fiscal Converted Date) as the leading date.
Note: This calculation is based on the difference in months. Analyzing the Fiscal Converted Date field by days may present deviations due to the fact that not all months have the same number of days.
To follow the same example, February will now be presented as December. Meaning, December 31st will have no data because it is now presenting the data of February 31st which, do not exist.
