3 Replies Latest reply on Jan 24, 2019 1:11 PM by maneesh.gaddam

# How to calculate last 6 months calculated field

Hi I'm trying to create a rolling 6 months calculated field (to show the last 6 months beginning from previous month ). I'm doing it as follows:

IF DATEDIFF('month', [Create Date], today()) <=6

AND [Create Date] < today()

then [Create Date]

end

But this calculates the last 6 months beginning from this month. i.e., gives the values as July 2018 August 2018 September 2018 October 2018 November 2018 December 2018 January 2018

But I want the values as June 2018 July 2018 August 2018 September 2018 October 2018 November 2018 December 2018

because currently January month is going on and is not complete.

How do I do that?

• ###### 1. Re: How to calculate last 6 months calculated field

Anjali,

you could try

and

[Create Date]<=DATE(DATETRUNC('month',TODAY())-1)

• ###### 2. Re: How to calculate last 6 months calculated field

Hi Maneesh,

Your calculation is close but if you subtract a month from the current date, and then datetrunc it, it's only going to take create dates from on or before the first of the previous month.  For example, subtracting one month from January will give you December, then doing a date trunc on that will give you December 1st - so anything created later on in December wouldn't be included

This slight modification will make it work:

and

Best,

Paul

• ###### 3. Re: How to calculate last 6 months calculated field

Hi Paul,

Thanks for the mention. Maybe I'm reading the OP's post incorrectly but if [Created date] is at a day level, then using

[Create Date]<=DATE(DATEADD('day', -1, DATETRUNC('month',TODAY()))) we aren't gonna pickup anything that is created after 12/01. So that's why I created DATE(DATETRUNC('month',TODAY())-1) which picks end of month (12/31).

As the OP said, he/she wanted to include December 2018, 12/01 wouldn't pick whole of december data, right?