Use the Calculated Month Field
if DATEDIFF('month',[Order Date],TODAY())<=5 and DATEDIFF('month',[Order Date],TODAY())>=0 then [Order Date] END
instead of your actual Date field. Replace [Order Date] in the formula with your actual Date column
Do you have Reporting Month (Month - Year) as drop down selection? If yes, is it parameter or filter?
1 of 1 people found this helpful
I use this for my Rolling 6 Month logic:
DATETRUNC('month', DATEADD('month',-6, TODAY())) <= [Order Date] AND [Order Date] <= TODAY()
I add to my filter shelf & return only True.
I'm interpreting your question that you want to show multiple months and not a single moving average number. If you want a single number then one of the other solutions posted here will work.
We can get a rolling or moving average from a view with Month as a dimension and a measure (like SUM(Sales) here) in a few clicks.
1) I right-clicked on SUM(Sales) and chose Quick Table Calculation->Moving Average.
2) I right-clicked again on the SUM(Sales) pill and choose Edit Table Calculation to bring up the Table Calculation window.
3) I edited it to include the previous 5 values:
Here's the view:
If you want to filter for certain months then you need to make sure the filter is applied *after* the table calculation is computed, the easiest way to do that is to use a calculated field that is a table calc such as LOOKUP(DATETRUNC('month',MIN([Order Date])),0) and then put that on the Filters Shelf.
v10.0 workbook is attached, let me know if you have any questions!
moving avg of sales.twbx 1.5 MB
Right now in this worksheet I have not kept any filter or parameter Drop down.