Date calculations (DATEDIFF, DATEADD) in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to understand how sales have changed month over month and to forecast sales for the next month based on current trends.

📊 Data: You have a sales dataset with columns: Order Date, Sales Amount, and Region. The data covers sales for the first six months of the year.

🎯 Deliverable: Create a Tableau dashboard showing monthly sales totals, month-over-month sales difference, and a forecasted sales value for the next month.

Sample Data

Order Date	Sales Amount	Region
2024-01-15	1200	North
2024-01-28	800	South
2024-02-10	1500	North
2024-02-20	700	South
2024-03-05	1800	North
2024-03-18	900	South
2024-04-12	2000	North
2024-04-25	1100	South
2024-05-08	2100	North
2024-05-22	1200	South
2024-06-15	2300	North
2024-06-28	1300	South

1

Step 1: Create a calculated field to extract the month and year from Order Date for grouping.

Create calculated field 'Order Month' with formula: DATETRUNC('month', [Order Date])

Expected Result

Each order is assigned to the first day of its month, e.g., 2024-01-01 for January orders.

2

Step 2: Create a monthly sales total measure by summing Sales Amount grouped by 'Order Month'.

Use SUM([Sales Amount]) aggregated by 'Order Month' in the view.

Expected Result

Monthly sales totals: Jan=2000, Feb=2200, Mar=2700, Apr=3100, May=3300, Jun=3600.

3

Step 3: Create a calculated field to find the month-over-month sales difference using LOOKUP.

Create calculated field 'MoM Sales Change' with formula: SUM([Sales Amount]) - LOOKUP(SUM([Sales Amount]), -1)

Expected Result

Shows difference in sales compared to previous month: Feb=200, Mar=500, Apr=400, May=200, Jun=300.

4

Step 4: Create a calculated field to forecast next month's sales by adding the last month's sales change to the current month's sales.

Create calculated field 'Next Month Forecast' with formula: LOOKUP(SUM([Sales Amount]), 0) + LOOKUP(SUM([Sales Amount]) - LOOKUP(SUM([Sales Amount]), -1), -1)

Expected Result

Forecasted sales for July: 3600 + 300 = 3900.

5

Step 5: Build a line chart with 'Order Month' on the x-axis and SUM(Sales Amount) on the y-axis to show monthly sales trend.

Drag 'Order Month' to Columns, SUM([Sales Amount]) to Rows.

Expected Result

Line chart showing increasing sales from January to June.

6

Step 6: Add 'MoM Sales Change' as a label or tooltip to the line chart to show month-over-month differences.

Add 'MoM Sales Change' to Tooltip or Label shelf.

Expected Result

Hovering over points shows sales change compared to previous month.

7

Step 7: Add a text box or KPI card showing the 'Next Month Forecast' value.

Display calculated field 'Next Month Forecast' as a single value.

Expected Result

Dashboard shows forecasted sales for July as 3900.

Final Result

Monthly Sales Trend

Jan |■■■■■■■■■■■■■■■■■■ 2000
Feb |■■■■■■■■■■■■■■■■■■■■ 2200 (+200)
Mar |■■■■■■■■■■■■■■■■■■■■■■■■■ 2700 (+500)
Apr |■■■■■■■■■■■■■■■■■■■■■■■■■■■ 3100 (+400)
May |■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 3300 (+200)
Jun |■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ 3600 (+300)

Next Month Forecast: 3900

✓Sales have steadily increased each month from January to June.

✓The largest month-over-month increase was from February to March (+500).

✓The forecast predicts July sales will continue to grow to 3900.

Bonus Challenge

Create a calculated field to compute the percentage change month-over-month and add it to the dashboard.

Show Hint

Use the formula: (SUM([Sales Amount]) - LOOKUP(SUM([Sales Amount]), -1)) / LOOKUP(SUM([Sales Amount]), -1) * 100

Practice

1. What does the Tableau function DATEDIFF('day', #2024-01-01#, #2024-01-10#) return?

easy

A. 10

B. 11

C. 9

D. Error

Solution

1. Step 1: Understand DATEDIFF parameters

   DATEDIFF counts the number of boundaries crossed between two dates in the specified unit, here 'day'.

2. Step 2: Calculate days between 2024-01-01 and 2024-01-10

   From Jan 1 to Jan 10, there are 9 full days difference, but DATEDIFF counts the number of day boundaries crossed, which is 10 (Jan 1 to Jan 2 is 1, ... Jan 9 to Jan 10 is 9, plus the starting boundary counts as well).

3. Final Answer:

   10 -> Option A

4. Quick Check:

   DATEDIFF('day', start, end) counts days crossed = 10 [OK]

Hint: DATEDIFF counts boundaries crossed, not total days [OK]

Common Mistakes:

• Counting both start and end dates as full days
• Confusing DATEDIFF with DATEADD
• Using wrong unit like 'month' instead of 'day'

2. Which of the following is the correct syntax to add 3 months to a date field [Order Date] in Tableau?

easy

A. DATEADD([Order Date], 3, 'month')

B. DATEADD('3 months', [Order Date])

C. DATEDIFF('month', [Order Date], 3)

D. DATEADD('month', 3, [Order Date])

Solution

1. Step 1: Recall DATEADD syntax

   DATEADD takes three arguments: date part as string, number to add, and the date field.

2. Step 2: Match correct argument order

   DATEADD('month', 3, [Order Date]) matches syntax: DATEADD('month', 3, [Order Date]). Others have wrong order or wrong function.

3. Final Answer:

   DATEADD('month', 3, [Order Date]) -> Option D

4. Quick Check:

   Correct DATEADD syntax = DATEADD('month', 3, [Order Date]) [OK]

Hint: DATEADD('unit', number, date) is the correct order [OK]

Common Mistakes:

• Swapping argument order
• Using DATEDIFF instead of DATEADD
• Passing units as part of number argument

3. What is the result of this Tableau calculation?
DATEDIFF('week', #2024-01-01#, DATEADD('day', 15, #2024-01-01#))

medium

A. 2

B. 3

C. 1

D. 0

Solution

1. Step 1: Calculate DATEADD('day', 15, #2024-01-01#)

   Adding 15 days to Jan 1, 2024 results in Jan 16, 2024.

2. Step 2: Calculate DATEDIFF in weeks between Jan 1 and Jan 16

   Weeks crossed: Jan 1 to Jan 8 (1 week), Jan 8 to Jan 15 (2 weeks), Jan 15 to Jan 16 does not complete another week. So total 2 weeks difference.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   DATEDIFF('week', start, end) counts full weeks crossed = 2 [OK]

Hint: Add days first, then count weeks crossed [OK]

Common Mistakes:

• Counting partial weeks as full weeks
• Mixing up order of DATEADD and DATEDIFF
• Using wrong date formats

4. You wrote this Tableau formula but it gives an error:
DATEADD('day', '5', [Ship Date])
What is the problem?

medium

A. The date field [Ship Date] must be a string

B. The date part 'day' should be in uppercase

C. The number of days should not be in quotes

D. DATEADD cannot add days, only months

Solution

1. Step 1: Check argument types in DATEADD

   DATEADD expects the second argument as a number, not a string.

2. Step 2: Identify error cause

   Using '5' (string) instead of 5 (number) causes a type error.

3. Final Answer:

   The number of days should not be in quotes -> Option C

4. Quick Check:

   Number argument must be numeric, not string [OK]

Hint: Numbers in DATEADD must be numeric, no quotes [OK]

Common Mistakes:

• Putting numbers in quotes
• Assuming case sensitivity for 'day'
• Thinking DATEADD only works with months

5. You want to find how many full quarters have passed between #2023-02-15# and a date 200 days later. Which Tableau formula correctly calculates this?

hard

A. DATEDIFF('month', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) / 3

B. DATEDIFF('quarter', #2023-02-15#, DATEADD('day', 200, #2023-02-15#))

C. DATEDIFF('quarter', DATEADD('day', 200, #2023-02-15#), #2023-02-15#)

D. DATEADD('quarter', 200, #2023-02-15#)

Solution

1. Step 1: Calculate the date 200 days after Feb 15, 2023

   Using DATEADD('day', 200, #2023-02-15#) gives the target date.

2. Step 2: Use DATEDIFF with 'quarter' to count full quarters passed

   DATEDIFF('quarter', start_date, end_date) counts how many quarter boundaries are crossed.

3. Step 3: Evaluate options

   DATEDIFF('quarter', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) correctly uses DATEDIFF with 'quarter' and correct date order. DATEDIFF('month', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) / 3 divides months by 3 but may give decimals, not full quarters. DATEDIFF('quarter', DATEADD('day', 200, #2023-02-15#), #2023-02-15#) reverses dates causing negative result. DATEADD('quarter', 200, #2023-02-15#) misuses DATEADD.

4. Final Answer:

   DATEDIFF('quarter', #2023-02-15#, DATEADD('day', 200, #2023-02-15#)) -> Option B

5. Quick Check:

   Use DATEDIFF('quarter', start, end) for full quarters [OK]

Hint: Use DATEDIFF with 'quarter' and correct date order [OK]

Common Mistakes:

• Dividing months by 3 instead of using 'quarter'
• Swapping start and end dates
• Using DATEADD instead of DATEDIFF for difference