How do I filter for a relative date that started the first of the month 7 months ago?

[Lauren Marshall]

If today is June 18th, I need a start date of Dec 1 of the prior year and an end date of May 31st of the current year. 

[Akriti Sagar · Accepted solution]

Hi Lauren,

Great question! In Jira JQL (Jira Query Language), you can’t directly reference specific first-of-month or end-of-month dates with a built-in relative date function, but here’s a way to filter the range you need:

If today is June 18, and you want issues from December 1 of the previous year through May 31 of this year, use absolute dates like this:

created >= "2023-12-01" AND created <= "2024-05-31"
If you want this to stay relative as time moves forward, Jira doesn’t support dynamic "first day of X months ago" natively in JQL. You’d need to update the dates each month manually or use an automation rule or scripted field (like with ScriptRunner) for more dynamic logic.

Let me know if you’re using ScriptRunner or need a workaround using automation rules to make this dynamic month-to-month.

Best regards,
Akriti Sagar

[Lauren Marshall]

Thanks for the quick response Akriti! I do use ScriptRunner in Jira but my question was specific to Atlassian Analytics (which I am just now learning). Does ScriptRunner work in AA? 

[Jessie Turpin]

Hi @Lauren Marshall ScriptRunner doesn't work within Atlassian Analytics. You'd likely want to use a relative date variable {TODAY} paired with the .SUB() function to subtract 7 months from today's date. 

[Lauren Marshall]

@Jessie Turpin Unfortunately, that won't work as I need to get the first of the month that was 7 months ago and the last day of the prior month. So if If today is June 20th, I need a start date of Dec 1 of the prior year and an end date of May 31st of the current year. 

[Jessie Turpin]

Thanks for clarifying, @Lauren Marshall! In that case, I'd recommend using the {CURRENT_MONTH.START} and {CURRENT_MONTH.END} variables paired with the .SUB() function to grab the start and end date of the month that was 7 months ago.