4 Replies Latest reply on Sep 13, 2017 6:49 AM by Alexander Elfering

# Using a calculated field to pull a specific month?

Hello,

So I know that you can create a calculated field to pull data for a specific month such as:

IF YEAR([Date]) = 2017 THEN [Measure] END

Is it possible to create a similar calculated field to instead pull a specific month/year, such as July 2017?

Thanks,

Alex

• ###### 1. Re: Using a calculated field to pull a specific month?

Hi Alex,

Have you tried IF YEAR([Date]) = 2017 AND MONTH([Date]) =  7 THEN [Measure] END?

Regards,

Ivan

• ###### 2. Re: Using a calculated field to pull a specific month?

Thanks

Deepak

• ###### 3. Re: Using a calculated field to pull a specific month?

DATETRUNC will get you there. DATETRUNC('month',[DATE]) returns a datetime field that is the first of every month.

1/1/2017, 2/1/2017, 3/1/2017, etc. These can be formatted to show Jan 2017, Feb 2017, etc.

This is how DATETRUNC works with various date parts:

 Date DatePart TRUNC('DatePart',[Date]) 08/30/2017 13:52:09 minute 08/30/2017 13:52:00 08/30/2017 13:52:09 hour 08/30/2017 13:00:00 08/30/2017 13:52:09 day 08/30/2017 00:00:00 08/30/2017 13:52:09 week 08/27/2017 00:00:00 08/30/2017 13:52:09 month 08/01/2017 00:00:00 08/30/2017 13:52:09 quarter 07/01/2017 00:00:00 08/30/2017 13:52:09 year 01/01/2017 00:00:00

So then, IF DATETRUNC('month',[Date]) = #1/1/2017# THEN ... END

Hope this helps,

Jennifer

• ###### 4. Re: Using a calculated field to pull a specific month?

Perfect. Thank you kindly!