Forum Discussion
window function
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.
Hi Jeanxyz ,
Your query for calculating the 2-day moving average looks mostly correct. The issue you're facing with sorting might be due to how the result set is being handled after the window function is applied.
Here’s a couple of things to check:
- Sorting in the final result: Even though you're using ORDER BY inside the OVER() clause, that only affects the window calculation — not the final output order. If you want the result to be sorted by date, you need to add an ORDER BY Date at the end of your query:
SELECT
ZKey,
Max_Close,
Date,
AVG(Max_Close) OVER (
PARTITION BY ZKey
ORDER BY Date
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
) AS MovingAvgClosePrice
FROM TableName
ORDER BY ZKey, Date;Data type issues: If Date is stored as a string or not properly typed, the ordering might not behave as expected. Make sure it’s a proper DATE or DATETIME type.
If you're using a tool like Power BI or Excel: Sometimes the visual layer overrides the sort order. In that case, sort the visual explicitly by Date.
Let me know if you’re working in a specific SQL engine (like BigQuery, SQL Server, etc.) — some syntax might vary slightly.
If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI
burakkaragoz ,Thanks a lot for replying. You are right about filter execution and below are my tries:
I. calculated column that works
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))