The Calendar year runs from Jan 1st to Dec. 31st, however, some organizations may customize their date fields according to Fiscal dates.
A Fiscal date (for example, a financial year, or sometimes budget year) is a period used for calculating financial statements in businesses and other organizations.
When measuring certain KPI’s based on fiscal dates, it is necessary to build this field in the ElastiCube.
This article demonstrates how to build a Fiscal date in the ElastiCube and Fiscal calendar hierarchies in the dashboard.
A sales company measures its revenues based on a fiscal year that starts on Sept. 1st and ends on Aug. 31st.
Create a Date dimension table (Here’s a link on how to create Date dimension table ), which contains 2 fields: The Calendar date field and a Fiscal date field, which is a computed field. To create this field, use the addmonths function. Here’s a link with an explanation of the function.
In this example, we are adding 4 months to the Calendar date, where a value such as “9/1/2015” becomes “1/1/2016”:
SELECT distinct SOH.OrderDate AS CalendarDate, Addmonths(SOH.OrderDate,4) AS FiscalDate FROM [Sales.SalesOrderHeader] SOH
Link the Date dimension table to the fact table based on the Calendar date field.
Use case 1 - Fiscal year with Calendar months
Now you can present the Calendar months aligned with the Fiscal year within the same widget as displayed in the image below:
Use case 2 - Fiscal Date hierarchy with Calendar date
You can present the Calendar dates as part of the Fiscal Date Hierarchy
1. Create a Fiscal Hierarchy in the dashboard. Here’s a link on how to create hierarchies in the dashboard.
2. Define the Hierarchy in the widget
3. Choose the FiscalDateHierarchy for the Fiscal Date field
Now you can view the revenue per calendar date
Advance usage of Fiscal date - MTD, QTD, YTD
You can extend the date dimension table you’ve created above with YTD/QTD/MTD flags for the Fiscal-Date
The Fiscal YTD / QTD / MTD flags define whether or not a specific date should be included in the value calculation:
In order to do so,please use the following syntax -
SELECT FiscalDate, CalendarDate, CASE WHEN [Fiscal_thisQTRIndex]>=[FiscalQTRIndex] THEN 1 ELSE 0 END Fiscal_isQTD, CASE WHEN [Fiscal_thisYTDIndex]>=[Fiscal_YTDIndex] THEN 1 ELSE 0 END Fiscal_isYTD, CASE WHEN [Fiscal_thisMonthDay]>=[Fiscal_monthDay] THEN 1 ELSE 0 END Fiscal_isMTD FROM (SELECT *, [Fiscal_MonthIndex]*100+getday(FiscalDate) FiscalQTRIndex, [Fiscal_thisMonthIndex]*100+getday(addmonths(now(),4)) Fiscal_thisQTRIndex, getmonth(FiscalDate)*100+getday(FiscalDate) AS Fiscal_YTDIndex, getmonth(addmonths(now(),4))*100+getday(addmonths(now(),4)) AS Fiscal_thisYTDIndex, getday(FiscalDate) Fiscal_monthDay, getday(addmonths(now(),4)) Fiscal_thisMonthDay from (SELECT DISTINCT FiscalDate, dimdate.CalendarDate, toint(CASE WHEN tostring(getmonth(FiscalDate)) IN ('1','4','7','10') THEN '1' WHEN tostring(getmonth(FiscalDate)) IN ('2','5','8','11') THEN '2' WHEN tostring(getmonth(FiscalDate)) IN ('3','6','9','12') THEN '3' END ) Fiscal_MonthIndex, toint(CASE WHEN tostring(getmonth(addmonths(now(),4))) IN ('1','4','7','10') THEN '1' WHEN tostring(getmonth(addmonths(now(),4))) IN ('2','5','8','11') THEN '2' WHEN tostring(getmonth(addmonths(now(),4))) IN ('3','6','9','12') THEN '3' END ) Fiscal_thisMonthIndex FROM [DimDate] ) dimDates1 ) dimDates2
Step 2 -
Add the Fiscal_isMTD / Fiscal_isQTD / Fiscal_isYTD =1 flag as a “measured value” within the formula editor (assist this documentation):