2 Replies Latest reply on Jan 14, 2019 11:21 AM by Daniel Lawrence

# Do not show current period for all time granularity

Hello all,

I am looking to make a responsive formula for a line graph that chops off the current period.  Seems simple enough, but I want it to respond to what ever time period the user selects cycling through the Date hierarchy.

So, if line graph is in Months it does not show the current month, if it is in quarters, it does not show the current quarter, etc....

I am trying to use a very similar formula as was found in this thread, but I keep running into the problem of double booleans.

Specifically:

IF

MIN(DATEPART('dayofyear',[Date]))=MAX(DATEPART('dayofyear',[Date]))

THEN

Date < Today()

END

The intent here is for it to detect that the granularity is day and exclude today but the problem is that the THEN statement has a boolean which needs to be asserted to TRUE or FALSE as in a filter.

I think this requires a neat, clever trick to work.  Unfortunately, I am neither neat or clever so I present this to you as a challenge.

Good luck and thank you!

• ###### 1. Re: Do not show current period for all time granularity

Simple enough...

Use this as a filter:

IIF([Date] > DATETRUNC([Time Param], TODAY()), False, True)

I assume your hierarchy is done through a parameter of ['quarter', 'month', 'week', etc]. If they select quarter, then it will filter out all dates that are past the current time period's start date.

• ###### 2. Re: Do not show current period for all time granularity

Joseph,

Thank you for the reply!  When you say [Time Param] do you mean an explicit Parameter that is user selected?  What I was referring to is the + - boxes as depicted below.  In the thread listed in the original post, we figured out how to make tooltip calculations responsive to what ever time period is selected through via these buttons.