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, 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].

Need Help

Jeanxyz
1 year ago
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.

9 Replies

pankajnamekar25
Super User
1 year ago
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
  Power Participant
  1 year ago
  Thanks. It doesn't work because there are public holidays and weekend, hence it's not possible to define the starting date.
burakkaragoz
Super User
1 year ago
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
- Jeanxyz
  Power Participant
  1 year ago
  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
    Super User
    1 year ago
    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
Anonymous
Not applicable
1 year ago
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

Solution Sage

1 year ago

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
Power Participant
1 year ago
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.

Forum Discussion

window function

9 Replies

Related content

Window Function for Student marks

DAX SQL window function equivalent

DATESYTD function

Reverse Running Total Measure per Category/Bin (WINDOW Function) - Avoiding Multiple Tables

function behavior