Some Methods for Calculating Quarter in DAX for Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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:

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.

the output is:

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

the output is:

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

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;

the output is:

RoundUp

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

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:

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

and EndOfQuarter:

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

3 thoughts on “Some Methods for Calculating Quarter in DAX for Power BI

Leave a Reply

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