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