Customer's fiscal year does not comply with the Georgian 12-month calendar and starts on a different date than January 1st.
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.
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.
We will create another field in out dim date which will calculate the new fiscal date for each existing date.
Assumption, the fiscal year starts on April 1st. In case the fiscal year doesn't start on April, simply replace the 4 with the appropriate month number.
Step 1 – Calculate the date difference between customer's fiscal year and regular fiscal year:
CASE WHEN (getmonth(Date))>=4 THEN
END AS DayDifference
- Set the field to be of type INT (in case this is in a new field in a source table)
Step 2 – Create converted date field:
adddays([Date], -[DayDifference]) 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 days, which means that the 1st of the month in the fiscal year may not be the 1st of the month in the actual year (because different months have different number of days).
If you wish to just replace the months, for instance call April the first month (regardless of April not having 31 days like January), then use monthdiff (instead of dayfdiff) is step 1 and addmonths (instead of adddays) in step 2.