How to Display Running Total of Daily Budget Considering Missing Dates

Published: 27 Oct 2023
Last Modified Date: 31 Oct 2023

Question

When the Sales data have missing dates and the Budget has values in year and month, how can we display running total of daily budget considering missing dates?

User-added image

If there are missing dates, the running total of daily budget is not displayed expectedly, as shown below.

Environment

Tableau Desktop

Answer

The attached sample workbook shows an example of displaying the running total of the daily budget with a straight line so that they are in line with the expected Budget number when there are some missing sales dates .

User-added image

1. Combine the tables as follows:
Note: Calculated fields are used to combine the tables by year and month.
　 User-added image

2. Create a calculated field and name it Budget per day.

[Budget (Budget)] / DAY(DATEADD('day',-1,DATEADD('month',1,[Year and Month])))

3. Create a calculated field and name it Budget per day (replace null with previous value).

IF ZN(LOOKUP(SUM([Sales (Sales)]),0)) =  0 THEN
    PREVIOUS_VALUE(SUM([Budget per day]))
ELSE
    SUM([Budget per day])
END

4. Create a calculated field and name it Running total of daily budget.

RUNNING_SUM([Budget per day (replace null with previous value)])

5. Add discrete YEAR(Date), MONTH(Date) and DAY(Date) to Columns.
6. Right-click Year(Date) in Columns and select Show Missing Values.
7. Add SUM(Sales (Sales)) to Rows. Change Mark type to Bar.
8. Right-click SUM(Sales (Sales)) in Rows and select Quick Table Calculation > Running Total.
9. Add Running total of daily budget to Rows. Change Mark type to Line.
10. Right-click Running total of daily budget in Rows and select Dual Axis.
11. Right-click Right Y-axis and select Synchronize Axis.