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.
     
Elena_Kalina's avatar
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's avatar
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.