How to ignore Date Filters for Moving Sum Calculations?

Question

Hi all!

I have a table with the following columns/rows/measures:

And I am calculating moving sum for the metric value [1st SVC] like this:

WINDOW_SUM([1st SVC], -2, 0)

I want to display moving sums for each month. For eg. Expected output:

2-month for September-2023 will be sum of July, August & September Metric Value.

2-month for June-2023 will be sum of April, May & June Metric Value

And so on

But when I am applying filter on the date column, it is failing to calculate the moving sum only using the showed months to calculate it. Is there a way to ignore filters when calculating Moving Sum Calculations?

Best regards,

Viviane Lefebvre · Accepted Answer

Hi @Jorge Ramos​  Create this field LOOKUP([Sales Month],0) and use this instead to filter your data, it will come after table calc and should give you the correct result.   Learn more about Order of Operations here : https://help.tableau.com/current/pro/desktop/en-us/order_of_operations.htm   Hope it helps, Viviane Please mark as  Select as Best  if this resolved your question, or Upvote if it helped.

Viviane Lefebvre · Answer

It's a bit tricky, I would go with a totally different solution.   1. Create a set with your ​[Date Month]  field 2. Add the set first position in columns 3. Show set (will be used for selected month instead of a filter) 4. Hide the 'Out' column (this will work like your filter) 5. Change Rank1 to  [Rank1] = IIF(ATTR([MY Date Set]),RANK(SUM([Sales])),0) 6. WINDOW_SUM(([Rank1]),FIRST(),LAST())   This should give your your expected output. We're not filtering out data, we're just hiding them because we need them for the running_sum, and we make the rank to 0 when it's out of the set to get the right sum.   example attached. Hope it helps, Viviane   Please mark as  Select as Best  if this resolved your question, or Upvote if it helped.

How to ignore Date Filters for Moving Sum Calculations?

Tableau Desktop & Web Authoring

Related Learning

More Help