New Quarter DAX Time Intelligence Function

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

 

Posted in Blog and tagged .

Leave a Reply

Your email address will not be published. Required fields are marked *