4 Replies Latest reply on Oct 10, 2018 5:58 AM by meenu choudhary

# Create a formula that always looks at the second to last month in data

Hello. How can I create a filter and/or a formula that only looks at the second to last month and third to last month in a data set?

I want to always analyze the most recent COMPLETE month and then the month before it.

For example, I would like to see average price for September vs. August (right now)

• ###### 1. Re: Create a formula that always looks at the second to last month in data

Hello Alex,

Are you looking for this:

1. Current month = DATETRUNC('month',TODAY())

2. Last month = DATEADD('month',-1,[Current month])

3. 2nd last month = DATEADD('month',-2,[Current month])

4. avg sales for last month = AVG(if DATETRUNC('month',[Order Date])=[Last month] THEN [Sales] END)

5. avg sales for 2nd last month = AVG(if DATETRUNC('month',[Order Date])=[2nd last month] THEN [Sales] END)

If this post helped you to solved your query, please mark it as CORRECT and HELPFUL.

1 of 1 people found this helpful
• ###### 2. Re: Create a formula that always looks at the second to last month in data

Hello Alex Martino

This is for follow up. If your query is solved. Please mark my answer as correct and useful.

If not, please post your query here.

• ###### 3. Re: Create a formula that always looks at the second to last month in data

Hey! I thought I replied to this. How can I make "today" the last date in the data set.

• ###### 4. Re: Create a formula that always looks at the second to last month in data

Hello Alex,

You can replace "Today" with MAX([Date]).