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
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
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.
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.
Thank you for providing your feedback on the effectiveness of the article.