Please spare some time if you have a solution for this.
The requirement I have is to create a Daily Report (we will be selecting a Day in a filter).
I would need to create one column as an Average of last ten instances of each [keyword id], Like if we select 21Sep from our Filter So for each keyword present on that day, I would need to calculate Avg of last ten Instances of that [Keyword id] order by Date desc before the selected Day.so referring to Image below, for each keyword tableau should go before 21sep and pick ten instances of that [Keyword id] and calculate Avg of Cost for that ten days, For Ref:
We will be applying a filter so we need to go for a LOD here so we can calculate on back days, I tried to partition and rank based on keywordid,Day and sum but No avail. Here is the SQL query ran to achieve this:
Query: select account,day,keywordid,cost/clicks AS CPC,row_number() over (partition by keywordid order by day desc) from [PPC.Keywords] where keywordid='10405032' order by day desc
Note: I need as a calculation so I can perform some more formulas.so Index() with edited table calculation can`t work I guess.