5 Replies Latest reply on Jul 18, 2018 10:16 AM by Vinnie Ahuja

# Most Recent Week Available - Date Calc needed

I am a beginner at Tableau and I'm sure this topic has been discussed numerous times.

Here's my issue:

I am creating a dashboard that updates every Thursday afternoon with new data. Since the data is only updating once a week, I would like for the most recent week of data to show until the new data is available.

Example:

The current data available is 7/2/2018 - 7/6/2018, but on Thursday afternoon data for 7/9/2018 - 7/13/2018 will be available.

This is the current formula I am using. The problem with this formula is I have to change the below weekly. I either change it to -6 or -13, depending on what day of the week it is. I need assistance.

I have attached my workbook and I am using version 10.5.

• ###### 1. Re: Most Recent Week Available - Date Calc needed

I have dealt with a similar issue in the past.  The key is to have your week start on Thursday, since that is the day of the new data.  To solve my issue, I created the following Boolean calculated field and filtered for TRUE to exclude incomplete weeks.  Each Thursday, you should get a new week of data.

[Your Date Field] <= DATEADD('week',-1, DATETRUNC('week',today(), 'thursday'))

This essentially checks your date field to make sure it is less than or equal to last week, with Thursday being the first day of each week.

• ###### 2. Re: Most Recent Week Available - Date Calc needed

Hi Justin...

So if your week resets on SUNDAY, that means you need to change your formula only when the day is Friday and Saturday, correct?

You could use an IF or CASE statement with DATENAME('weekday',NOW()) to change the formula you used based on the weekday name.

You can even use DATENAME('hour',NOW())) to confirm it's past noon on Thursday, too.

--Michael

• ###### 3. Re: Most Recent Week Available - Date Calc needed

Would this be the new calc? If so, how do I fix the error?

• ###### 4. Re: Most Recent Week Available - Date Calc needed

Which portion in the formula would I need to change?

• ###### 5. Re: Most Recent Week Available - Date Calc needed

you wouldn't need the entire ELSE clause.  You only need the portion I provided above.  Also, it appears that you're using a parameter here?  You wouldn't need a parameter in the method I showed, you would use the date field of interest from your data.