Tableau - How to Compute and Filter Prior Business Day

Use this procedure to filter data by the last business date rather than the maximum or current business date.

Tableau has the ability to display the maximum and minimum values for data, including date fields.  It also has the ability to filter by date ranges or relative dates – such as today or yesterday.
 
The problem is that it is sometimes necessary to compare data from the current business day to the prior business day.  The existing “yesterday” filter works if the business operates seven days a week or if the report is generated Tuesday through Friday.  However, if the report is generated on Monday and the business did not have any data from the weekend, then no data is returned in the report.
 
To work around this issue, it is necessary to create some calculated fields to figure out the current business day and the previous business day.  After finding those dates, it is necessary to filter the data based on the prior business day.

It is possible to calculate the prior business day based on the day of the week.  For example, one could state that the prior business day for Monday is Friday for a business that is only open on weekdays.  However, this logic breaks when Monday is a holiday because Tuesday will reference Monday as the prior business day.
 
Instead, it is better to find the maximum business date that is less than the reference date.
 
Note:  This procedure assumes that the data source has a timestamp field that shows when the data was entered in the database.  For this procedure, the timestamp is called [Compare Date].

Given the following distinct datetime stamps, it is easy to see the maximum date and the highest date prior to the maximum.
 
2016-03-30 00:00:0000
2016-03-31 00:00:0000
2016-04-01 00:00:0000
2016-04-04 00:00:0000
2016-04-05 00:00:0000
2016-04-06 00:00:0000
 
In the above example, the maximum date is 2016-04-06 (Wednesday) and the prior work day is one day less, or 2016-04-05 (Tuesday).  However, if one attempts to compare 2016-04-04 (Monday) to one day less, it would result in the date of Sunday -- not Friday. 
 
To get the prior business date, it is necessary to look at the current date being evaluated and the maximum date less than that date.   If one references 2016-04-06 (Wednesday), the maximum date that exists in the set less than that date is 2016-04-05 (Tuesday).  If one references 2016-04-04 (Monday), the maximum date that exists in the set less than that date is 2016-04-01 (Friday).  This procedure would also account for days that are not recorded, such as holidays.

Because Tableau functions solely by issuing SQL commands, it is necessary to perform a subquery.  However, Tableau does not support subqueries without creating a custom SQL statement at the data source.  For this to work, it is necessary to manipulate the “Level of Detail”, or LOD functions in Tableau. 
 
The LOD functions allow the workbook to change the granularity of an aggregated field (SUM, AVG, MAX, or MIN).  We will use the LOD function to find the maximum date less than the current date and will then return that as a date field.

1. Attach the necessary data sources and configure the relationships as needed.

2. Create a calculated field called "Calc_LastBusinessDate".  This field is going to be used to hold the date for the previous business date.
 

This field will get a list of the dates less than today(), get the maximum value from that list, and then calls it [Compare Date].
 
Formula:
 
{fixed: max(if [Compare Date] < today() then [Compare Date] end )}

This field holds the value of the date for the previous business day, but cannot be used to filter data dynamically.

3. Create another calculated field called “Calc_IfLastBusinessDate”.  This field is going to be used to compare if Calc_IfLastBusinessDate is equal to the [Compare Date] field. 

Formula:
 
[Calc_LastBusinessDate] = [Compare Date]
 

This field becomes a Boolean expression and CAN be used to filter the data dynamically.

4. Add the Calc_IfLastBusinessDate field to the "Filters" container. 

5. Edit the newly added filter.  Select True and then click OK.

6. The data displayed in the worksheet will now be filtered based on the previous business date.