I think your moving average is calculating correctly. If you only want to display feb or the last available month you can create a calculated field LAST()=0 which you will then drag to filters and set to True.
Here is a formula based on Superstore sales data
Monthly Average>> if Avg(DATEPART('month',[Order Date]))=1 then Null else
RUNNING_SUM(SUM([Sales]))/Avg(DATEPART('month',[Order Date])) end
You would need to sub in your metrics but this will calculate the average of the ytd sales divided by the number of months
Let me know if this helps
Teresa Wright wrote:
So far, I give Tableau a C+. You can do a lot with it, but Excel is so much easier to use. I am already tired of all the calculations that are needed for what should be a simple thing.
Part of your trouble is in that statement.
Tableau is not Excel and doesn't pretend to be. But when we pretend it is, we paint ourselves into messy corners.
Sometimes the people who struggle most with Tableau are the guru-level users of Excel.
Tableau is always a dimensional grid, not a spreadsheet. What tableau does to one "cell", it does to all cells in your sheet. (That's why you are still seeing January on your sheet.) And that's why it seems like you need to do complicated stuff to get what you want.
Having said that, let's look at what you need to do.
The average of two cells actually needs to be looked at as the average of all the cells across that dimension: in your case, for example, it's the average of all the [Outgoing] cells across the months. And each of those [Outgoing] values isn't just a single number. It's a SUM of all the rows that fall in January, and the SUM of all the rows that fall in February. MOVING_AVG works for you here because you have only two months selected. If you had three months of data, would you want two different values as the result? (AVG of Jan-Feb, and AVG of Feb-Mar.) Or would you want one overall average?
I could help you out more if I had a physical example of your data. I know you have proprietary issues, but we can share anonymized workbooks. Check out this thread:
I don't need all your data. Just a handful of rows for each month.