How to calculate the sum of 3 months in a data source with random dates.

Published: 01 Mar 2022
Last Modified Date: 02 Mar 2022

Question

How to calculate the sum of 3 months in a data source with random dates?
Example:
Record of 2022/5/5 : SUM of 2022/2/4 - 2022/5/4
Record of 2022/7/1 : SUM of 2022/3/30 - 2022/6/30

Environment

Tableau Desktop

Answer

1. Connect to Sample_Data.xlsx .
2. Right click and drag [Date] to Rows > Select Date(Discrete).
3. Right click [Date] and click [Show Missing Values].
4. Create a new calculated field below to calculate the date 3 months ago.
Field Name : Date 3 months ago
Calculation : DATEADD('month',-3,[Date])
5. To calculate the difference in the days from three months ago, create the following calculation field.
Field Name : Date difference
Calculation : DATEDIFF('day',[Date],[Date 3 months ago])
6. To calculate the total for the last 3 months, create a new calculation field below.
Field Name : Total_last 3 months
Calculation : WINDOW_SUM(SUM([Quantity]),ATTR([Date difference]),-1)
7. Drag [Total_last 3 months] to Columns.
8. Change the graph to Bar.
9. Right click [Total_last 3 months] > Edit Table Calculation > Setting as following.
User-added image

10. To use the Table Calculation Filter, create a new calculated field with the following settings.
Field Name : Filter
Calculation : lookup(min(([Date])),0)
11. Right click [Filter] > Click Convert to Continuous.
12. Drag [Filter] to Filter Pane and click OK.