Welcome to Excel Jet Consult blog post. In this tutorial, we want to learn various methods we can use to calculate standard Quarter and how to create using the new Quarter DAX function released in the Microsoft Power BI Desktop December 2019 updates. Let’s get started
To use the new Quarter DAX function, we must create a calendar table because the function work with Time Intelligence
In the caption below, we have created Calendar table with Month, Month Sort and Year calculated columns
ROUNDUP DAX Function Method
In the Calculations group of the contextual Table Tools tab, click on Calculated Columns
In the formula bar, execute the DAX formula: Roundup Method = ROUNDUP(‘Date'[Month Sort]/3,0)
In the caption below, we have the standard quarter: Jan – March which represent 1; April – June represent 2, and so on
VALUE and INT DAX Functions Method
In the Calculations group of the contextual Table Tools tab, click on Calculated Columns
In the formula bar, execute the DAX formula: Value & Format = VALUE(FORMAT(‘Date'[Date],”Q”))
In the caption below, we have the standard quarter: Jan – March which represent 1; April – June represent 2, and so on
Ceiling and Month Functions Method
In the Calculations group of the contextual Table Tools tab, click on Calculated Columns
In the formula bar, execute the DAX formula: Ceiling & Month = CEILING(MONTH(‘Date'[Date])/3,1)
In the caption below, we have the standard quarter: Jan – March which represent 1; April – June represent 2, and so on
New Quarter DAX Function Method
Having see how to calculate standard quarter using three different methods, we want to see how to do the same using the new Quarter DAX function.
QUARTER DAX returns a number from 1 (January – March) to 4 (October – December) representing each quarter
QUARTER Syntax =QUARTER(Date)
In the Calculations group of the contextual Table Tools tab, click on Calculated Columns
In the formula bar, execute the DAX formula: New QUARTER DAX = QUARTER(‘Date'[Date])
In the caption below, we have the standard quarter: Jan – March which represent 1; April – June represent 2, and so on
Conclusion
The new Quarter DAX function undoubtedly makes it easier to calculate standard quarter compared to the slightly three technical methods described. Looking forward to the possibility of having FISCALQUARTER DAX function in the nearest future