Last Modified Date: 04 Jan 2017
AnswerUse the following formulas as templates from which to create calculated fields. In the formulas below, Date is the name of the date dimension.
Filtering a Range of Days
Use the following formula to create a calculated filed that filters for the last n days:
IF [Date] < (today() - <n> + 1) OR [Date] > today() THEN NULL ELSE [Date] ENDFor example, a calculated field that filters data for the last 90 days would have the following formula:
IF [Date] < (today() - 90 + 1) OR [Date] > today() THEN NULL ELSE [Date] ENDFormula for the last 7 days:
IF [Date] < (today() - 7 + 1) OR [Date] > today() THEN NULL ELSE [Date] END
Filtering WeeksUse the following formula to filter data for the week before last:
IF [Date] > (today() - 8 + 1) OR [Date] <= (today() - 15 + 1) THEN NULL ELSE [Date] ENDUse the following formula to filter for the last eight weeks:
IF DATEPART('week', [Date]) <DATEPART('week', TODAY()) - 8 OR DATEPART('week', [Date]) > DATEPART('week', TODAY()) -1 THEN NULL ELSE [Date] END
- You can use a date parameter, another field, or a fixed date in place of TODAY() in order to base the calculation on a different anchor date.
- The calculations can be modified with any of the date_part values listed in Date Functions to filter different date levels.
To view the above examples in action, see the video below.
Note: the Sample - Superstore data source's maximum date is January 6, 2016 for for the Shipping date dimension. This means that there we are not able to use TODAY() but we use a substitute.
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials