Situation: You have daily sales data, but you don’t want to include the current week until it closes. You also don’t want to manually filter the most recent week out of the report each week because that’s an unnecessary nuisance.
Solution: Create two date calculations and one filter calculation
Step 1 – Create a calculated field to get the current year & week
Note that I’m creating a string that includes the year. This is necessary if you have more than one year in your reports. If the formula only returns the week number, then that week number would be excluded for all years.
Step 2 – Create a calculated field to get the year & week for each date of your date field
Step 3 – Create a calculated field to act as the filter
Step 4 – Drag the Exclude Current Week field to the Filters shelf and choose the Exclude option on the bottom right when the result of the calculation is “EXCLUDE”
An alternative solution would be to build the two calculated field from step 1 & 2 into the calculation built in step 3, but I chose to break them out separately for this example so that you can more easily see how the calculations work together.
The calculation would look like this:
This method would work if you wanted to ONLY KEEP the current week. In this case, you would follow step 4, but you would NOT select the Exclude checkbox on the bottom right.