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()
Thank you Nick! I forgot that quarters could be subjective…
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!