No QUARTER() in DAX? Really?

9
votes

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?

6
ssasbusiness-intelligencepowerbidaxssas-tabular
There are other ways besides IFs or SWITCH: you can use (MONTH([Date])+2)/3 – Rory
Thanks, Rory, for idea! – erop
Hi @erop, have you chosen an answer? – user8065556
@drmyrnz, see here stackoverflow.com/a/32586553/409317 – erop
@erop Ah, cool. Glad you came to a solution. :) – user8065556

6 Answers

13
votes

I found an answer in this great book!

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

7
votes

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.
4
votes

It's not documented but this works:

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

3
votes

DAX now has quarters! This is some date data: Dates

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

The results of these are below:

Date quarter slicers

0
votes

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).

0
votes

Add Colom for get Quarter : Quarter = summary_bu_USD[Start Period].[Quarter]

Add colom again and group year in colom : QuarterYear = YEAR(summary_bu_USD[Start Period])&" "&"Q"&RIGHT(summary_bu_USD[Quarter],1)

enter image description here