Different organizations have different classification for fiscal quarters and different fiscal years. Even within a big enterprises, different teams might have different fiscal quarters and this might even change from year to year. Regardless we have the flexibility to do this Sigma easily.
Let’s take a quick example and say my fiscal year starts on July and ends June of next year.
I have my PLUGS dataset here with granular date and time.
Always good practice to keep the original date column as is for QA or other downstream analysis later on.
For our quarters we can simple isolate the months form our date we need for each quarter and categorize as such. We can use a combination of If(), Month(), and In() functions.
Formula; If(In(Month([Date]), 7, 8, 9), “Q1”, In(Month([Date]), 10, 11, 12), “Q2”, In(Month([Date]), 1, 2, 3), “Q3”, “Q4”)
Logic:
if (
month of date is in 7, 8, 9 then return “Q1”
month of date is in 10, 11, 12 then return “Q2”
month of date is in 1, 2, 3 then return “Q3”
else “Q4” )
For the fiscal year let’s make a new column and get our fiscal year form the PLUGS date column with a combination if If(), Month(), and Year() functions. Fiscal years are generally 1 calendar year ahead of the calendar year.
Formula; If(Month([Date]) >= 7, Year([Date]) + 1, Year([Date]))
Logic; if the month of the date is greater than or equal to July (7), then add 1 to the Year of Date, else return the Year of Date.
In other words for July, August, Sept, Oct. Nov, and Dec in 2023, this will return 2024 fiscal year but January to June 2024 will remain the same.
Now that we have both fiscal year and our quarters defined, we can do quick check by adding a new column to get the Year-Month from the date column by using DateTrunc() function.
Formula; DateTrunc(“month”, [Date])
Logic; DateTrunc(“month”, [date column]) will isolate the Year-Month for display purposes and do not actually remove the day values. Where as the above function Year() and Month() will remove other components form the date and only return the specific portion of the date that is asked for.
Let’s group our newly created column, [Quarters], [Fiscal Year], [Month of Date], and check if everything aligns.
Simply drag and drop the columns on the left side panel as such;
And to adjust this, simply change the Fiscal year start month and adjust the months in the quarters formula. Lets say my fiscal year starts on February and ends on January of next year.
Change starting month of fiscal year to 2 in the formula;
If(Month([Date]) >= 2, Year([Date]) + 1, Year([Date]))
In the quarters we simply adjust the months for each quarter;
If(In(Month([Date]), 2, 3, 4), “Q1”, In(Month([Date]), 5, 6, 7), “Q2”, In(Month([Date]), 8, 9, 10), “Q3”, “Q4”)
Now that everything looks aligned, we can also do some formatting to to combine Fiscal Year and Quarter into one value by using Concat() and Text() functions.
Formula; Concat(Text([Fiscal Year]), “-”, Text([Quarters]))
Logic: Concatenate Fiscal Year (as text datetype), add hyphen “-”, and Quarters into one value.
A final check with the new FY-Quarters column and Month of Date will return similar results.
If you have any questions on the above please feel free to comment below. The beauty and flexibility of Sigma is that there might be other more clever ways of doing this and if you do please share them in the comments below or create a new post even!