Forum Discussion
How to remove context from a temporary contextually-calculated table-variable ?
Within the definition of the column you're adding using ADDCOLUMNS you can manipulate the filter context in any way you want, or you can manipulate the filter context in a CALCULATETABLE as in the above example where the date is being manipulated.
My final result is the curve of the moving average over time. As to what that is, each data point of the moving average would be the average of the 3 preceiding data points, the current one and the next 3 from the underlying measure. (See this wikipedia article for further detail: https://en.wikipedia.org/wiki/Moving_average)
I have managed to create that moving average as a calculated column with another column as underlying. What I am trying to do here is have it as either a column or a measure based on an underlying that is a measure. I want that this way so that it can be dynamically filtered using an important range of possible filters.
The way that I wanted to process was by having my underlying data copied and filtered in a temporary table, to which I would add the in-context measure and calculate the average of the column of the whole table.
I would love it if you had any idea of a possible solution
The general method is to use ADDCOLUMNS .. SUMMARIZE to build the temporary table with an additional column containing the value you want to average, then use AVERAGEX over that table.
For example, if you wanted the average sales by brand over a given period you could use something like
Average by brand over time =
VAR MinDate =
MIN ( 'Date'[Date] )
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR SummaryTable =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( Sales, 'Date'[Year month], 'Product'[Brand] ),
"@sales amount", CALCULATE ( SUM ( 'Sales'[Amount] ) )
),
'Date'[Date] >= MinDate
&& 'Date'[Date] <= MaxDate
)
VAR Result =
AVERAGEX ( SummaryTable, [@sales amount] )
RETURN
Result
Thank you for trying to help me.
I still get the same result as my underlying measure, as my context is still inherited from the underlying. Is there no way to extand the time context to the adjacent entries ?Within the definition of the column you're adding using ADDCOLUMNS you can manipulate the filter context in any way you want, or you can manipulate the filter context in a CALCULATETABLE as in the above example where the date is being manipulated.
Thank you very much that was really helpfull. I didn't know you could redefine the context like that.