Skip to main content

Hi,

 

So earlier in the year this worked, but now it stopped working for dates in the future. I created several sets with custom calculations, in combination with a case statement set on the worksheet, and a parameter, to filter the worksheet for various time periods: this month, next month, this quarter, next quarter.

 

I created four sets with the following calculations:

 

DATEPART('month', [Due for Signature]) = (DATEPART('month', TODAY() ) )

 

DATEPART('month', [Due for Signature]) = (DATEPART('quarter', TODAY() ) )

 

DATEPART('month', [Due for Signature]) = (DATEPART('month', TODAY() ) + 1 )

 

DATEPART('month', [Due for Signature]) = (DATEPART('quarter', TODAY() ) + 1)

 

If I create a parameter for This Month, Next Month, This Quarter, and Next Quarter, then This Month and This Quarter correctly filter the worksheet. However, if I choose Next Month or Next Quarter the data disappears even though there are rows for those timeframes. If I change the calculations to look back in the year, for example:

 

DATEPART('month', [Due for Signature]) = (DATEPART('month', TODAY() ) - 1 )

DATEPART('month', [Due for Signature]) = (DATEPART('quarter', TODAY() ) - 1 )

 

The worksheet correctly filters to the previous month and previous quarter. The only thing I can think of is that we're in the month of December and so the "+ 1" in the Next Month and Next Quarter calculations would show data from the next calendar year. Is that why it's no longer working? Is there any way I can fix these calculations so they show data from Next Month and Next Quarter even if we're at the end of the calendar year? The fiscal year for the Due for Signature date field is set to October.

7 answers
  1. Dec 11, 2020, 5:53 PM

    The reason for this is that DATEPART will just return the month--it drops the year. Instead of DATEPART, change all of the calcs to use DATETRUNC. Everything else should be the same.

0/9000