Forum Discussion

Jeanxyz's avatar
Jeanxyz
Power Participant
1 year ago
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, ...
Need Help
  • Jeanxyz's avatar
    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.
     
Anonymous's avatar
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