I have a problem that that shouldn't give much trouble, but I can't make the sense out of this.
I have a measure called m_test as follows:
WINDOW_SUM(ATTR({ FIXED [Period]: COUNT([ID])}),-([p_mvavg_num_periods]),0)
This is supposed to calculate the moving sum of the count of IDs with a parameter selection for the number of periods at the FIXED LOD for [Period].
My view has [m_test] at Columns Shelf, and [Period] at rows shelf. And so far, so good, the view show the correct values regardless of [p_mvavg_num_periods] selected.
However, when I put the column [Classification] as color markers AND [p_mvavg_num_periods] > 0, the view shows different values depending on the [Classification] as shown below:
I recognize that this happens because the [Classification] = "Class3" only exists for [Period] = 2023-T2 and not for previous periods, but I assumed that using the Fixed [Period] would work because it would calculate at the [Period] level and would not be affected by [Classification] at the color markers.
How can I do this calculation without being affected by the color markers?
Constraints: Need to use the [p_mvavg_num_periods] as this parameter is being used for other purposes in my dashboards.
I have attached the sample data source and sample twbx file for your consideration.
