DateTime Function by Quarter

[briankuhlmann]

I need to configure a start date and an end date for a Dynamic Input. End date is easy, last day of previous month. Start Date needs to be first date of the 5th quarter back. For instance, in this case I would need start Date to be April 1 2021 because it would give me Q2, Q3, Q4 of 2021 and Q1, Q2 of 2022, five quarters in total.

Next month, my end date would last day of July but my start date would need to change to July 1 2021 because it would give me five quarters again. Q3, Q4 of 2021 and Q1, Q2, Q3 of 2022. 

I  basically need data from 5 full quarters at the end of a quarter or 4 full quarters and a current quarter at the end of a standard month.

I'm not even sure this is possible, any help appreciated

[dinesha]

we can make use of Case with some programming 

can you please share the datasheet 

regards

dinesha 

[briankuhlmann]

at this point in the workflow there is no data yet., i am trying to produce a start date for a sql query in a dynamic input. The start and end date fields replace dummy strings in a where invoicedate between '01/01/1900' and '01/02/1900'

currently i have the eays formula for end date and have been typing start date manually every month.

[DataNath · Accepted solution]

How does this look @briankuhlmann? Not sure this is the most succinct way of doing so, but the Start Date expression basically trims today's date down to the beginning of the current quarter, before subtracting 15 months to roll back the 5 quarters:

[briankuhlmann]

Looks good, thank you!