Calculated Column for previous trading day close price and previous 3 trading days avg close price

[Anonymous]

I am new to DAX and looking for some help. 

I do not want to create a new measure but want to create new calculated columns. The final requirements to create measures will depend on these Calculated Columns.

I have below sample dataset to work with in Tabular Model (DateCounterID is brought from Date table using related function). Date table keeps track of Trading Days and adds a counter to DateCounterID. in Date table, the dates trading didn't happen DateCounterID is populated as 0. My Goal is to have few more calculated columns created to store "Previous Trading Day's Close Price", 3 Days Average of Closing Price ( 3 day avg is average of 3 previous day's closing price), and then 5 Days Average , 10 Days Average etc.

TickerID	Date	ClosePrice	DateKey	DateCounterID
1	1/3/2017	38	20170103	10750
2	1/3/2017	50.87	20170103	10750
1	1/4/2017	38.29	20170104	10751
2	1/4/2017	51.12	20170104	10751
1	1/5/2017	38.57	20170105	10752
2	1/5/2017	51.75	20170105	10752
1	1/6/2017	37.91	20170106	10753
2	1/6/2017	51.15	20170106	10753
1	1/9/2017	37.31	20170109	10754
2	1/9/2017	50.61	20170109	10754
1	1/10/2017	37.11	20170110	10755
2	1/10/2017	50.62	20170110	10755
1	1/11/2017	37.55	20170111	10756
2	1/11/2017	51.21	20170111	10756
1	1/12/2017	37.76	20170112	10757
2	1/12/2017	51.81	20170112	10757
1	1/13/2017	37.66	20170113	10758
2	1/13/2017	51.59	20170113	10758
1	1/17/2017	37.44	20170117	10760
2	1/17/2017	51.51	20170117	10760
1	1/18/2017	37.1	20170118	10761
2	1/18/2017	51.19	20170118	10761
1	1/19/2017	36.9	20170119	10762
2	1/19/2017	50.66	20170119	10762
1	1/20/2017	36.84	20170120	10763
2	1/20/2017	51.16	20170120	10763
1	1/23/2017	36.61	20170123	10764
2	1/23/2017	50.91	20170123	10764
1	1/24/2017	36.91	20170124	10765
2	1/24/2017	51.23	20170124	10765
1	1/25/2017	37.03	20170125	10766
2	1/25/2017	51.48	20170125	10766
1	1/26/2017	36.74	20170126	10767
2	1/26/2017	50.79	20170126	10767
1	1/27/2017	36.58	20170127	10768
2	1/27/2017	50.4	20170127	10768
1	1/30/2017	35.96	20170130	10769
2	1/30/2017	49.82	20170130	10769
1	1/31/2017	35.98	20170131	10770
2	1/31/2017	50.56	20170131	10770

[Stachu]

I assume the averages are for last x trading days, not calendar days, correct?
try this code

PreviousClosePrice = 
VAR __TickerID = 'Table'[TickerID]
VAR __DateCounterID = 'Table'[DateCounterID]
VAR __PreviousDateCounterID = CALCULATE(MAX('Table'[DateCounterID]), FILTER(ALL('Table'), 'Table'[TickerID]=__TickerID && 'Table'[DateCounterID]<__DateCounterID))
VAR __PreviousClosePrice = CALCULATE(MAX('Table'[ClosePrice]), FILTER(ALL('Table'), 'Table'[TickerID]=__TickerID && 'Table'[DateCounterID]=__PreviousDateCounterID))
RETURN
__PreviousClosePrice

this average includes the current row trading day close price in the average, if you want to exclude it replace blue code with __PreviousDateCounterID. To change number of days change the value in __NrOfDaysToAverage

3DayAverageClosePrice = 
VAR __TickerID = 'Table'[TickerID]
VAR __DateCounterID = 'Table'[DateCounterID]
VAR __PreviousDateCounterID = CALCULATE(MAX('Table'[DateCounterID]), FILTER(ALL('Table'), 'Table'[TickerID]=__TickerID && 'Table'[DateCounterID]<__DateCounterID))
VAR __NrOfDaysToAverage = 3
VAR __AverageClosePrice = 
    CALCULATE(
        AVERAGE('Table'[ClosePrice]), 
        FILTER(ALL('Table'), 
               'Table'[TickerID]=__TickerID && 
               'Table'[DateCounterID] <= __DateCounterID && 
               'Table'[DateCounterID] > __DateCounterID - __NrOfDaysToAverage
        )
    )
RETURN
__AverageClosePrice

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

[Anonymous]

Hi there.

Mate, this transaction table has the potential to be really big. If you use CALCULATE in a calculated column, be prepared that such code could return after a very, very, very long time or even not at all. This is due to CONTEXT TRANSITION which is an expensive operation and I've seen models where executing it in in rows of a table brought the whole model down to the point where it was unusable. CALCULATE should not be used in this case. One can rephrase this calculation using FILTER only and *X functions.

Best

Darek