If you are a beginner user to Tableau, you may not be aware of how to get a rolling 90 day or 120 day calculated field in Tableau. You may know how to filter a dashboard by the last 3 months and the current year. But what if you wanted the last 90 days or the last 120 days?
I created this dashboard here so you can see a 90 day or 120 day rolling calculation. I even calculate back for one year of data based on the selection.
I will talk about two different methods for obtaining the rolling sum.
Calculated field using an Level Of Detail Expression
First you need to create a calculated field to always pull the Max Date or the most recent date from your data. You can do this with the calculation below;
Max Date
{ Fixed : MAX([Order Date])}
Then you need to create a calculated field to give you the date from 3 months ago;
3 Mo
Date(Dateadd('month',-2,[MaxDate]))
Then you can use these calculations to obtain the total Sales or Profit for this range;
3 Mo Sales
IF DATEPART('year',[Order Date]) = Datepart('year',[MaxDate])
AND (DATETRUNC('month',[Order Date])>= DATETRUNC('month',[3 Mo])
AND DATETRUNC('month',[Order Date])<= DATETRUNC('month',[MaxDate]))
THEN [Sales]
END
With my data set the last day was December 30th 2021. You can see below it provided data back to October for the Sales measure. Notice these calculations are looking at month intervals and not day intervals.
But what if your user wanted to pick a date and see the last 90 days, 120 days or even 1 year back for that date? The next method I will review is with using a Date Parameter as your anchored date.
Rolling Sum Method with a Date Parameter
Create a Parameter as shown below;
Then you will need to create your calculated fields pointing to this Parameter.
90 Days Ago
Date(Dateadd('day',-90,[Date]))
120 Days Ago
Date(Dateadd('day',-120,[Date]))
365 Days Ago
Date(Dateadd('day',-365,[Date]))
Next you will need the series of calculations for the BAN or KPI worksheet.
I created six min(1) calculations for the column row. Don't forget to click Shift Enter after you type in the header name or you can copy and paste the calculations I have below;
//Sales
Min(1)
//120 Days Sales
Min(1)
//1 Year Sales
Min(1)
//Profit
Min(1)
//120 Days Profit
Min(1)
//1 Year Profit
Min(1)
Then you need to create the Calculated fields for each Marks card that is generated for each of these columns;
90 Days Sales
IF DATETRUNC('day',[Order Date])>= DATETRUNC('day',[90 Days Ago])
and DATETRUNC('day',[Order Date])< DATETRUNC('day',[Date])
THEN [Sales]
END
120 Days Sales
IF DATETRUNC('day',[Order Date])>= DATETRUNC('day',[120 Days Ago])
and DATETRUNC('day',[Order Date])< DATETRUNC('day',[Date])
THEN [Sales]
END
1 Year Sales
IF DATETRUNC('day',[Order Date])>= DATETRUNC('day',[365 Days Ago])
and DATETRUNC('day',[Order Date])< DATETRUNC('day',[Date])
THEN [Sales]
END
You then can duplicate each of these calculations and change the [Sales] to [Profit].
Place each of these calculations on the corresponding marks card. Then format the text by clicking on the Label marks card and clicking on the ellipsis button.
Change the Mark type on the All marks card to Shape and use a Blank shape to remove the highlight feature. Then change the text alignment to center. I also removed gridlines and other lines from the view.
I then created some line graphs and other visualizations using these calculated fields. Feel free to download the workbook to see how the other visualizations are configured.
If you place these calculations on a sheet you can check the values that are reflected based on the Date Parameter. In the example below I selected 9/08/2021 for my parameter, the days change based on that selection. It will not include data for 9/01/2021 like the previous method, because we are using day as the granularity for this method and not month.
You can also replace the Date Parameter in these calculations with the Max Date LOD expression, if you wanted the calculation to be dynamic and always point to the max date of your data set.