No QUARTER() in DAX? Really?

Question

While building a Calendar table with PowerQuery for a PowerPivot model in Excel 2013 I use its Date.QuarterOfYear function to get the number of the quarter.

Building the same thing in SSAS Tabular requires some workarounds. There's no equivalent DAX function to get number of the quarter from a DATE. Strangely, DAX has YEAR() and MONTH(), but no QUARTER().

Are nested IF or SWITCH statements really the only way in DAX to get the quarter number?

What is the reason for the absence such a simple and useful function? Am I overlooking the supreme wisdom of this decision?

Avalon

There are other ways besides IFs or SWITCH: you can use (MONTH([Date])+2)/3 – Dailey

Thanks, Rory, for idea! – Avalon

Hi @erop, have you chosen an answer? – Cupola

@drmyrnz, see here https://mcmap.net/q/1040901/-no-quarter-in-dax-really – Avalon

@Avalon Ah, cool. Glad you came to a solution. :) – Cupola

According to learn.microsoft.com DAX does have QUARTER function now. But I can`t make it. I upgraded SSAS, VisualStudio, SSDT, model level to 2019 and I still get errors "can not resolve the name quarter".Could you help me please? – Hoop

Answer 1

I found an answer in this great book!

One should use =ROUNDUP(MONTH([Date])/3, 0) to get quarter number.

Avalon

Answer 2

No QUARTER() in DAX?

Yes, that is correct.

Really?

Yes, it's crazy and doesn't make any sense. Fortunately the workaround is just dividing the month by 3.

Solution:

VAR quarterNumber = CEILING(MONTH([Date])/3,1)

Alternate Solutions:

Since DAX has multiple ways to round numbers, these will also work:

VAR quarterNumber = ISO.CEILING(MONTH([Date])/3,1)
VAR quarterNumber = ROUNDUP(MONTH([Date])/3,0)

Which Solution is Best:

• For the values used in my examples, the results will be identical.
• For other examples there can be small and subtle differences in the result depending on standards or the type of CPU being used.
• ROUNDUP is probably more intuitive to Excel people.
• CEILING is probably more intuitive to math people.
• ISO.CEILING is ugly to look at in code, personal opinion.

Pesky

Answer 3

It's not documented but this works:

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

Cavalcade

This looks like it might be slower, because it's also converting to a string then back to a number again. – Pesky

Answer 4

DAX now has quarters! This is some date data:

And these are how you get the quarters and quarter numbers:

The results of these are below:

Cupola

Answer 5

I think they are assuming you'd create a date dimension in which your Quarter is pre-defined, including the Financial Year. I live in Australia where the Financial Year ends in June, and I've always pre defined the quarters as an added column to the table. IF you're using Power BI/Power Query you can add a query in the M code level (at the import stage).

Farman

Answer 6

Add column for get quarter :

Quarter = summary_bu_USD[Start Period].[Quarter]

Add column again and group year in column:

QuarterYear = YEAR(summary_bu_USD[Start Period])&" "&"Q"&RIGHT(summary_bu_USD[Quarter],1)

enter image description here

Baranowski

Answer 7

You can also use this command:

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

And combined them to create things like this

="Q." & FORMAT(Date[Date],"q") & " - "&Date[Year] = e.g. Q.1 - 2021

Devoice