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

How Generative AI Can Be Used for Data Augmentation to Improve Data Quality in Enterprise Analytics

Data quality plays a major role in enterprise analytics to generate accurate and reliable insights. This requires clean, comprehensive and diverse data sets to make informed business decisions. But even today, many organisations struggle with incomplete or unstructured data and...

How to Scale Your On-Premise Tableau Server to Optimize Performance.

In all our previous blogs, we have stressed about the importance of Tableau and its various forms. Today, we will throw some light into how having a high-performing Tableau Server is crucial for fast and efficient analytics. Tableau Server is...

Creating a Website Analysis Dashboard in Tableau using Google Analytics

By  Kritika Singh, Senior Data Analyst, CRG Solutions Using GA as a connector:  Connecting Tableau to Google Analytics is a powerful way to analyse and visualize your website data. In this blog, you can follow through the steps to set...

Archives

Archives

Share this post

Leave a Comments

Please Fill Your Details






    Error: Contact form not found.