You may consider it somewhat silly to have a recipe for determining the quarter of a date since quarter is included in the default hierarchy for a date. However, many organizations do not adhere to a standard calendar quarter. For example, Microsoft's fiscal calendar runs from June to May. Other organizations start and stop their quarters on specific dates of the year. This quarter calculation can be used with standard quarterly calendars as well as non-standard quarterly calendars. It is easily modified to fit just about any custom quarterly schedule you can imagine. The quarter table that we will create in this recipe has the following quarters:

• Q1: August 15 – November 14
• Q2: November 15 – February 14
• Q3: February 15 – May 14
• Q4: May 15 – August 14

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R02_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

To implement this recipe, perform the following steps:

1. Create a column in the R02_Calendar table using the following formula:

QuarterNonStandard = 
VAR __Q1StartMonth = 8                   // Starting Q1 month number
VAR __Q1StartDay = 15                    // Starting day number of Q1
VAR __Q2StartMonth = 11                  // Starting Q2 month number
VAR __Q2StartDay = 15                    // Starting day number of Q2
VAR __Q3StartMonth = 2                   // Starting Q3 month number
VAR __Q3StartDay = 15                    // Starting day number of Q3
VAR __Q4StartMonth = 5                   // Starting Q4 month number
VAR __Q4StartDay = 15                    // Starting day number of Q4
VAR __Year = YEAR('R02_Calendar'[Date])  
VAR __date = 'R02_Calendar'[Date]        
//
// Do not modify below this line
//
VAR __QuarterMonths =               
    {   __Q1StartMonth, 
        __Q2StartMonth, 
        __Q3StartMonth, 
        __Q4StartMonth 
    }
VAR __MaxQuarterStartMonth = MAXX(__QuarterMonths,[Value]) 
VAR __Quarter = 
    SWITCH(
        TRUE(),
        __Q1StartMonth = __MaxQuarterStartMonth &&  
            (   
                __date >= DATE(__Year,__Q1StartMonth,__Q1StartDay) ||
                __date < DATE(__Year,__Q2StartMonth,__Q2StartDay)
            ),"Q1",  
        __Q2StartMonth = __MaxQuarterStartMonth &&  
            (
                __date >= DATE(__Year,__Q2StartMonth,__Q2StartDay) || 
                __date < DATE(__Year,__Q3StartMonth,__Q3StartDay)
            ),"Q2",
        __Q3StartMonth = __MaxQuarterStartMonth &&  
            (
                __date >= DATE(__Year,__Q3StartMonth,__Q3StartDay) || 
                __date < DATE(__Year,__Q4StartMonth,__Q4StartDay)
            ),"Q3",
        __Q4StartMonth = __MaxQuarterStartMonth &&  
            (
                __date >= DATE(__Year,__Q4StartMonth,__Q4StartDay) || 
                __date < DATE(__Year,__Q1StartMonth,__Q1StartDay)
            ),"Q4",
        __date >= DATE(__Year,__Q1StartMonth,__Q1StartDay) &&          
            __date < DATE(__Year,__Q2StartMonth,__Q2StartDay),"Q1",
        __date >= DATE(__Year,__Q2StartMonth,__Q2StartDay) &&           
            __date < DATE(__Year,__Q3StartMonth,__Q3StartDay),"Q2",
        __date >= DATE(__Year,__Q3StartMonth,__Q3StartDay) &&           
            __date < DATE(__Year,__Q4StartMonth,__Q4StartDay),"Q3",
        __date >= DATE(__Year,__Q4StartMonth,__Q4StartDay) &&           
            __date < DATE(__Year,__Q1StartMonth,__Q1StartDay),"Q4",
        "Error"                                                         
    )
RETURN __Quarter

The first eight lines are designed for the user to define the starting numeric months and days on which their quarters begin. The ninth line grabs the year from the current date and the tenth line simply gets the date from the current row. These ten variables are then used throughout the remainder of the DAX calculation to determine to which quarter a date belongs.

Following along with the calculation, the next step is to create a table called __QuarterMonths. The DAX table constructor is used along with the starting month variables __Q1StartMonth, __Q2StartMonth, __Q3StartMonth, and __Q4StartMonth. On the next line, we calculate the maximum value of these starting months using the MAXX iterator function. It may not seem like it, but this is the real magic of this recipe. We need to know which quarter might roll over between years. In the example, that quarter is Q2, since part of Q2 is in one year (November and December) and part is in the next year (January and February).

The first four conditions of the SWITCH statement are designed to catch the condition where a quarter rolls over from one year to the next. The only quarter that can possibly roll over from one year to the next is the quarter with the highest number for its starting month. Thus, this is the first check of each of these first four conditions. If this statement is true, then we know that the quarter might roll over from one year to the next, and so we check whether the date is greater than or equal to the starting date of the current quarter or less than the starting date of the next quarter. We use the DATE function to construct these dates from the variables set at the start of the recipe. We must have one of these statements for each quarter, since we have no idea where an organization might begin or end their quarters.

Once we account for the odd case of a quarter rolling over from one year to the next, the next four conditional statements are fairly straightforward. We can again construct dates using the DATE function for the starting and ending of quarters and simply check whether the current date under consideration is greater than or equal to the current quarter start date and less than the next quarter's start date. Again, we need one of these for each quarter.

Although the last condition for Q4 could have been replaced by the catch-all else condition of the SWITCH statement, it was instead decided to make the SWITCH statement conditions all inclusive and return an error if one of the conditions is not met. In other words, instead of checking explicitly for Q4, we could have had the last value for the SWITCH statement simply be Q4 instead of Error, the thought being that if none of the other conditions are met, the value must be Q4.

However, because this recipe is specifically designed for the user to enter their own values, it was determined safer to identify any odd condition as an error rather than masking it with a potentially incorrect Q4 value.

The really nifty part is that this recipe also works for standard calendar quarters!

First, to convert this DAX calculation from a column to a measure, simply edit these two lines as follows:

VAR __Year = YEAR(MAX('R02_Calendar'[Date]))    
VAR __date = MAX('R02_Calendar'[Date])          

Second, a company may have a really obscure method of assigning quarters, such as the first Monday in October, or the first working day in October. Since this technique only relies upon assigning numeric months and days for quarter starting dates, this recipe can be combined with other recipes in this book, such as those for finding the first working day of a month.

For more details regarding the functions in this recipe, refer to the following links:

• DATE: https://docs.microsoft.com/en-us/dax/date-function-dax
• Table constructor: https://docs.microsoft.com/en-us/dax/table-constructor
• MAXX: https://docs.microsoft.com/en-us/dax/maxx-function-dax
• SWITCH: https://docs.microsoft.com/en-us/dax/switch-function-dax