Not sure where the correct place to put this one is.
I am trying to get a formula or automation to update the period field in the attached screenshot based on the cadence. I have tried using automations, but it essentially circular references itself and won't allow me to select the Period to replace with Next Period, because Next Period is calculated based on Period and Cadence.
I have tried using a forumla in stead of an automation to make the Period date a live field but it only updates to the current month not the quarter and when the month is a non quarter ending period.
I have an automation created to create tasks on a new tab based on the period date monthly, but tasks that are quarterly don't need to be added until the last month of the quarter.
Essentially what I am looking for is a way to have
task 1 be monthly and update to the last date of the month every month so that the automation will create it monthly
Task 2 is quarterly and the period should only be the last month in the quarter not every month so that it doesn't flow into the automation if not applicable to the period
DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , 0, 'quarter'
)
