Forum Discussion
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, ...
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.
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.
Thanks. It doesn't work because there are public holidays and weekend, hence it's not possible to define the starting date.