Some Methods for Calculating Quarter in DAX for Power BI

You might have wondered that there is no DAX Quarter function in Power BI. Here in this short blog post, I explain some easy methods for calculating quarter. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Sample Data

To start this example, you can open a new Power BI file, create a new calculated table, with below expression that gives you a date table with one column:

Date = CALENDAR(
    DATE(2019,1,1),
    DATE(2020,1,1)
)

If you like to know more about calendar() function in DAX, read this article.

Using the Format Function

One easy way to calculate the quarter from a date field is the Format function with a format string of “q”. this will give you the Quarter number but as a text format.

FORMAT('Date'[Date],"q")

the output is:

You can then use a function such as INT if you want to achieve the number:

INT(FORMAT('Date'[Date],"q"))

the output is:

Or if you want to build a text value for it, such as QTR 1, you can do this:

FORMAT('Date'[Date],"\QTR q")

The \ in the format string above is an escape character, that means this Q after the \ will be an actual Q letter rather than the q that will be replaced by the quarter number.

the output is:

Calculating it from Month

Another way to calculate the quarter is to derive it from the month calculation. mainly a divide by three and the round it. Here are some options:

Ceiling

You can use the Ceiling function to the get the round-up number of the divide by three from the month value as below;

CEILING( MONTH('Date'[Date])/3 ,1)

the output is:

RoundUp

In this scenario, you can also use the roundup function to achieve the same thing with the same approach:

ROUNDUP( MONTH('Date'[Date])/3 ,0)

the output is the same:

Round

It is not my preference in this scenario to use Round, but if you want just to see another method to calculate it, here it is:

ROUND(
    (MONTH('Date'[Date])+1)/3
    ,0)

For this you need one extra step because round might end up with zero for some values, you have to first add one to the month value, and then do the divide and rounding. The output is the same.

Other Quater Related Functions

There are other functions that can be helpful for working with Quarters, such as StartOfQuarter

STARTOFQUARTER('Date'[Date])

and EndOfQuarter:

ENDOFQUARTER('Date'[Date])

Here is the output:

There are also two other functions: PreviousQuarter and the NextQuarter. However, these two functions don’t return one value, they return a range of dates, which can be useful for filtering other functions.

Summary

This was a very quick blog post about some of the ways you can calculate the quarter in DAX for Power BI. Most of the methods are the same anyways. If you like to learn more about Time Intelligence functions in DAX read more here.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.