Geek Answers Handbook

No QUARTER () in DAX? Indeed?

When creating a calendar table with PowerQuery for PowerPivot model in Excel 2013, I use the function

+6
source share
3 answers

I found the answer in this big book!

To get the Thursday number, use =ROUNDUP(MONTH([Date])/3, 0) .

+9
source

DAX does not have QUARTER ()?

Yes this is correct.

Really?

Yes, that shit does not make any sense. Fortunately, a workaround is simply dividing the month by 3.

Decision:

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

Alternative solutions:

Since DAX has several ways to round numbers, they will also work:

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

Which solution is best:

  • For this calculation, the results will be identical.
  • For calculations other than QUARTER, the result may be small and subtle differences depending on the standards or type of processor used.
  • ROUNDUP is probably more intuitive for Excel people.
  • CEILING is probably more intuitive to math.
  • ISO.CEILING is ugly to look at the code, personal opinion.
+2
source

Not documented, but this works:

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

+1
source

More articles:


All Articles