In this tip, I take you through the 5 most common date functions in Tableau:

1. DATEPART
2. DATENAME
3. DATETRUNC
4. DATEADD
5. DATEDIFF

By the end of this video you will understand when to use them to meet your use case. Click here for the cheat sheet I created for these date calculations.

This tip comes from an exercise I did alongside DS21 to find different ways to compare to the prior 3 months. Owen Barnes came up with the method using the IN function upon which this tip is based.

It's been eight weeks since I've done Workout Wednesday. Sometimes you have to reprioritize things to get other things done. For me, WW was something I could cut out to free up more time for finishing the Makeover Monday book (pre-order here).

But I'm back and this week Rody gave us this challenge. Read all of the requirements here.

#WorkoutWednesday Week 23: How do Sales compare in the current period to the previous.
Giving a slight twist on some older challenges. Enjoy!https://t.co/gBn05E4gev@lukestanke @AnnUJackson pic.twitter.com/trqc3ZnT3G

— Rody Zakovich (@RodyZakovich) June 6, 2018

I had an idea straight away how to do this and in all it took about 30 minutes. The date offsetting took some tinkering, but the rest was pretty easy. I'm glad Rody is back from his hiatus too. His challenges aren't as brutal as Ann's.

Click on the image for the interactive version.

This week's tip comes from a question on The Information Lab's collaboration platform about returning the latest, previous and prior month values.

I'm using LOD expressions in this video along with the DATEDIFF and DATETRUNC functions. Below this video, you will see the calculations that I used if you want to copy/paste them for your own use.

Latest Month Sales

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=0
THEN [Sales]
END

Previous Month Sales

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=1
THEN [Sales]
END

Prior Month Sales

IF DATEDIFF('month',DATETRUNC('month',[Order Date]),{MAX(DATETRUNC('month',[Order Date]))})=2
THEN [Sales]
END

If you want the months to be labeled…

Latest 3 Months

DATEDIFF('month',
DATETRUNC('month',[Order Date]),
{MAX(DATETRUNC('month',[Order Date]))}) <= 2

Latest N Months

DATEDIFF('month',
DATETRUNC('month',[Order Date]),
{MAX(DATETRUNC('month',[Order Date]))}) < [How many months?]