Quarter() for dates

A date.quarter() would be nice. I’m surprised it’s missing.

Quarters can be subjective though. For example, January is Q1 for many people but for many others (including the company I work for) July is Q1. So, for example, right now I’m in FY19Q4. Come July I’ll be in FY20Q1 and it can be a bit confusing.

I use this to calculate the Quarter:

Switch(thisRow.[Expected Close Date].DateTimeTruncate("quarter").month(),1,"Q3",4,"Q4",7,"Q1",10,"Q2")

There may be nicer ways!

And this to calculate the Financial Year:

RelativeDate(thisRow.[Expected Close Date], 6).Year()

2 Likes

Thank you Nick! I forgot that quarters could be subjective…

1 Like

Another way of calculating my quarter starting in July is:

"Q"+toText(4-Remainder(thisRow.[Expected Close Date].DateTimeTruncate("quarter").Month(),4))

This way is potentially “cleverer” but the switch method is far more readable!