5 Replies Latest reply on Jun 4, 2012 1:15 PM by Tracy Rodgers

# Up to Last Month Calculation

Can someone please help me with a calculation to display a count 'up to last month'? I've attached a workbook for reference.

In the Membership sheet, I have three main objectives: show Total Members This Month, Total Members Last Month, and Total Members Change.

For Total Members Last Month, I'm trying to display the total number of members from the beginning of time up to the end of last month.

I'm currently using the calculation:

COUNTD(

IF DATEPART('month',[Date]) <= DATEPART('month',TODAY())-1

AND DATEPART('year',[Date]) <= DATEPART('year',TODAY())

THEN [Company ID] END)

Unfortunately this calculation does not work for the month of January since IF DATEPART('month',[Date]) <= DATEPART('month',TODAY())-1 will equal 0 and not 12.

Any suggestions would be greatly appreciated.

Thanks,

Grant

• ###### 1. Re: Up to Last Month Calculation

DATETRUNC('month',[Date])<TODAY()

• ###### 2. Re: Up to Last Month Calculation

Hi Richard,

Thank you for your suggestion. It works great!

For my particular situation, I had to tweak the calculation to:

COUNTD(

IF DATETRUNC('month',[Date])<DATETRUNC('month',TODAY())

THEN [Company ID] END)

Thanks again,

Grant

• ###### 4. Re: Up to Last Month Calculation

Slight variation of the same problem. I'm trying to calculate "up to yesterday" and am trying to use the following:

COUNTD(

IF DATETRUNC('day',[Date])<DATETRUNC('day',TODAY())

THEN [Company ID] END)

Here's the caveat - [Company ID] can have values of 0 and 1 and I only need the total count for [Company ID] = 1.

Any idea how to do that?

• ###### 5. Re: Up to Last Month Calculation

Hi Rishi,

Try creating a calculated field that find [Company ID]=1 first, then reference that calculation in the above calculation.

-Tracy