First off, apologies that I cannot provide an example of the data I am working with, as the arrangement my organization has with Tableau has me working mostly blind. I'll try to describe the problem as best I can.
The measure being used is SUM([Sales Count]).
We have an annual trends graph of this over the past 5 years. To get this graph, for [Status Month] = {1:12} we filter to [Status Month] = 12, [Time Period] to Past 5 years anchored to today. The resultant numbers and trends are what we would expect them to be.
The issue is when we try to change our unit of time to month. It seems the measure becomes cumulative per month up to the end of each year. That is to say, if we have 50 sales up to November 2021, and we have 7 sales in December 2021, the graph indicates 57 sale in December 2021.
The corollary of this is that in January 2022, the number of sales is the number of sales in January 2022 (e.g. 5).
We tried 2 related solutions.
Attempt 1: This month's cumulative sales count minus last month's cumulative sales count
Command:
SUM([Sales Count]) - LOOKUP(ZN(SUM([Sales Count])), -1)
Result: We get the trend line we want, except for January each year. In the above example, we would get 5 - 57 = -52 instead of 5 for January 2022. This was somewhat expected.
Attempt 2: We tried to filter January out with an IF statement
Command:
IF [Status Month] <> 1 THEN SUM([Sales Count]) - LOOKUP(ZN(SUM([Sales Count])), -1) ELSE SUM([Sales Count]) END
Result: Error Message: Cannot mix aggregate and non-aggregate comparisons or results in IF expression.
Has anyone met with similar situations before or have any solutions, retrieve the Monthly Sales Count? Would appreciate your help. Thanks!
