window function

[Jeanxyz]

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, for some reason, the calculated column failed because it can not rank Dim_Date[Date] field can't be sorted. Why is there a problem? There is Many-to-One relationship between fact_stocks[Date] and Dim_Date[Date].

[v-pgoloju]

Hi @Jeanxyz,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Try below dax measure as calculated column to resolve the issue

2day range = CALCULATE (AVERAGE ( fact_stocks[Close] ),WINDOW (-1, REL,0, REL,ALL ( fact_stocks ),ORDERBY ( fact_stocks[Date], ASC ),PARTITIONBY ( fact_stocks[Stock] )))

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thanks & Regards,
Prasanna Kumar

[Elena_Kalina]

Hi, @Jeanxyz 

Please, try this one:

2-Day Moving Average Correct = 
VAR CurrentDate = SELECTEDVALUE('Dim_Date'[Date])
VAR CurrentStock = SELECTEDVALUE('dim_stocks'[Stock])
VAR PreviousDates =
    FILTER(
        ALL('Dim_Date'[Date]),
        'Dim_Date'[Date] <= CurrentDate &&
        'Dim_Date'[Date] >= CurrentDate - 2
    )
RETURN
IF(
    COUNTROWS(PreviousDates) >= 2,
    AVERAGEX(
        TOPN(
            2,
            FILTER(
                ADDCOLUMNS(
                    PreviousDates,
                    "ClosePrice", 
                    CALCULATE(
                        SUM('fact_stocks'[Close]),
                        'fact_stocks'[Stock] = CurrentStock
                    )
                ),
                [ClosePrice] <> BLANK()
            ),
            [Date],
            DESC
        ),
        [ClosePrice]
    ),
    BLANK()
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Jeanxyz]

Thanks Elena. This function doesn't fully meet the need because if there are public holidays, the previous day will be current day -3. Also this approach costs lots of calculation capacity, hence might cause refreshing issues. That's also why I want to try windows function. 

[burakkaragoz]

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:

1. 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;

1. 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.

2. 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

[Jeanxyz · Accepted solution]

@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]

@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

[Jeanxyz]

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)       

)

[pankajnamekar25]

Hello @Jeanxyz 

try this measure

2Day_MA_Close =

AVERAGEX(

    DATESINPERIOD(

        'Dim_Date'[Date],

        MAX('Dim_Date'[Date]),

        -2,

        DAY

    ),

    CALCULATE(SUM('fact_stocks'[ClosePrice]))

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Jeanxyz]

Thanks. It doesn't work because there are public holidays and weekend, hence it's not possible to define the starting date.