Forum Discussion
Solved
window function
I have a table fact_stocks which provides close price per stock per day, I want to add a calculated table called 2-day range which calculates the 2-day moving average close price o. Below is my try, ...
burakkaragoz ,Thanks a lot for replying. You are right about filter execution and below are my tries:
I. calculated column that works
2-day moving average = calculate(average(fact_stocks[Close]),WINDOW( -1, REL, 0, REL,ORDERBY( Fact_stocks[Date], ASC), partitionby(fact_stocks[Stock])), all(fact_stocks))** this measure works because DAX executes from the right to the left so before the window() is executed, the filtering context has been changed by all(fact_stocks), hence the window function loop over the whole fact_stocks table.II. calculated column that doesn't work:2-day moving avg_bad=Calculate(Average(fact_stocks[Close]),Window(-1, REL, 0, REL, All(fact_stocks), Orderby(Dim_Date[Date]), PartitionBy(dim_stocks[Stock])))** this expression doesn't work suggests the filtering condition All(fact_stocks) does not overwrite the pre-existing row context from fact_stocks. I'm not sure why this is the case though. According to MS documentation, this parameter is used to define a table from which the output rows are returned. So this expression should overwrite any pre-existing filtering context.III. Measure that works:2-day moving avg(M) = averagex(window(-1,REL,0,REL, summarize(allselected(fact_stocks),Dim_Date[Date],dim_stocks[Stock]), orderby(Dim_Date[Date]), partitionby(dim_stocks[Stock])), calculate(average(fact_stocks[Close])))* in this measure, there is no pre-existing context, the summarize() defines the table and DAX expression loop over the summarize table and return the value as expected.
burakkaragoz ,Thanks a lot for replying. You are right about filter execution and below are my tries:
I. calculated column that works
2-day moving average = calculate(average(fact_stocks[Close]),
WINDOW( -1, REL, 0, REL,
ORDERBY( Fact_stocks[Date], ASC), partitionby(fact_stocks[Stock])), all(fact_stocks)
)
** this measure works because DAX executes from the right to the left so before the window() is executed, the filtering context has been changed by all(fact_stocks), hence the window function loop over the whole fact_stocks table.
II. calculated column that doesn't work:
2-day moving avg_bad=
Calculate(Average(fact_stocks[Close]),
Window(-1, REL, 0, REL, All(fact_stocks), Orderby(Dim_Date[Date]), PartitionBy(dim_stocks[Stock]))
)
** this expression doesn't work suggests the filtering condition All(fact_stocks) does not overwrite the pre-existing row context from fact_stocks. I'm not sure why this is the case though. According to MS documentation, this parameter is used to define a table from which the output rows are returned. So this expression should overwrite any pre-existing filtering context.
III. Measure that works:
2-day moving avg(M) = averagex(
window(-1,REL,0,REL, summarize(allselected(fact_stocks),Dim_Date[Date],dim_stocks[Stock]), orderby(Dim_Date[Date]), partitionby(dim_stocks[Stock])), calculate(average(fact_stocks[Close])))
* in this measure, there is no pre-existing context, the summarize() defines the table and DAX expression loop over the summarize table and return the value as expected.
Jeanxyz ,
The issue you're hitting with List.Average is likely due to how the [Value] column is being extracted. When you do:
Table.SelectRows(...)[[Value]]
…it returns a table, not a list, and List.Average expects a list. You need to convert that column to a list explicitly.
Try modifying that part like this:
List.Average(
List.FirstN(
List.Sort(
Table.SelectRows(SortedTable, each [DateValue] <= _[DateValue])[Value],
Order.Descending
),
3
)
)Notice the [Value] without double brackets — this returns a list instead of a table.
Also, make sure:
- Value is numeric (not text)
- DateValue is in proper date format
translation and formatting supported by AI
I'm sorry, but I really don't understand your query. Is this sql query or query run in vertipaq engine? Could you pls make change in my DAX expression directly?
2-day moving average = calculate(average(fact_stocks[Close]),WINDOW( -1, REL, 0, REL,ORDERBY( Fact_stocks[Date], ASC), partitionby(fact_stocks[Stock])), all(fact_stocks))