CODEWITHSUNDEEP
ssasbusiness-intelligencepowerbidaxssas-tabular
erop
erop

Reputation: 1590

No QUARTER() in DAX? Really?

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?

Upvotes: 10

Views: 20741

Answers (7)

Supriyana
Supriyana

Reputation: 1

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

Upvotes: 0

Lucas
Lucas

Reputation: 1

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

Upvotes: 0

user8065556
user8065556

Reputation:

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

Upvotes: 5

Hila DG
Hila DG

Reputation: 738

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

Upvotes: 0

Lee Whitney III
Lee Whitney III

Reputation: 10978

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.

Upvotes: 8

Kees Kuip
Kees Kuip

Reputation: 71

It's not documented but this works:

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

Upvotes: 5

erop
erop

Reputation: 1590

I found an answer in this great book!

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

Upvotes: 15

Related Questions