By Sreekesh Eyyapadi, Technical Lead, CRG Solutions
There are many date functions in Tableau. Some manipulate dates, some convert data to dates, some identify if data is a date. This article will run through the main date functions and give some usage examples
- Finding the latest DATE in the data source
{MAX([OrderDate])}
This formula will return the maximum date and is also usable in other calculations. It’ll appear in the list of available Dimensions for use elsewhere.
- The Tableau ISDATE function – is my DATE field recognised as a DATE?
ISDATE([Field])
To test which values are recognised by Tableau as being a valid date, use the ISDATE() function. The function is boolean, it returns True for records identified as dates and False for those Tableau doesn’t think are dates.
- Identify the latest month
DATETRUNC(‘month’,{MAX([OrderDate])})
This formula finds the latest date in the dataset, then sets that date to the first of the month.
- Identify the same MONTH last year
DATEADD(‘year’,-1,DATETRUNC(‘month’,{MAX([OrderDate])}))
Similar to above, but this time also incorporate a DATEADD to find the same month last year.
- First day of current MONTH
DATE(DATETRUNC(‘month’, [Date]))
- Last day of current MONTH
DATE(DATEADD(‘day’, -1, DATEADD(‘month’, 1, DATETRUNC(‘month’, [Date]))))
- First day of next MONTH
DATE(DATEADD(‘month’, 1,DATETRUNC(‘month’, [Date]))
- Last day of next MONTH
DATE(DATEADD(‘day’, -1, DATEADD(‘month’, 2, DATETRUNC(‘month’, [Date]))))
- First day of previous MONTH
DATE(DATEADD(‘month’, -1,DATETRUNC(‘month’, [Date]))
- Last day of previous MONTH
DATE(DATEADD(‘day’, -1, DATETRUNC(‘month’, [Date])))
- Fiscal YEAR calculation (Apr – Mar)
if datepart(‘year’,dateadd(‘month,9,Date))=2024 then ‘FY2024’ end
- Week number start at 1 for every new MONTH
int(datepart(‘day’,[Date])/7)+1
- Days since beginning of the quarter
DATEDIFF(‘day’, DATETRUNC(‘quarter’, TODAY(), TODAY()))
This is a practical application of DATETRUNC, when we need to calculate pro-rata amount. For example, usually Quarterly Target is provided and our data holds Sales at daily transaction level. To check if our current Sales is on track, number of days since beginning of the Quarter can be used to compute proportional Target and then matched with Sales amount so far in this quarter.
With little tweaks here and there we can similarly define other date calculations as well.
Recent Posts
Are We Ready for Nano-Targeting? How Agentic AI Is Transforming Hyper-Personalized Marketing Strategies
How are marketing techniques evolving in the age of shrinking attention spans and rising digital noise to attain real-time contextual communication? We have seen personalization evolve, that is, from putting a name in an email to showing an advertisement to...
What does decision intelligence at scale look like? Can agentic AI become every business leader’s secret weapon?
The current volatile business environment and unlimited amounts of data pouring in from every resource means that making a decision has never been more difficult or more important. From forecasting the shifts in the market to channelling a smooth supply...
Automating Ticket Management in Atlassian’s Jira Service Management: From Requests to Resolution
Customer support and internal IT teams now have to work faster and more efficiently. For many organizations, Jira Service Management by Atlassian is the ultimate tool to track service requests, incidents and more. With more tickets coming in, it is...
Leave a Comments
You must be logged in to post a comment.