topic Re: Calculated Column for previous trading day close price and previous 3 trading days avg close pri in DAX Commands and Tips

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

Anonymous — Tue, 23 Jul 2019 02:11:16 GMT

am new to DAX and looking for some help.

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 closing price, 10 Days Average closing price etc.

I think we need to create calculated column to save previous trading day's close price. This can not be done in a measure as there is no aggregation. Also, the final requirements to create measures will depend on these Calculated Columns when users will be selecting Date range from PowerBI. Say a measure would be % increase in Stock Price for the selected range where we need to use closing price of previous day on start day and closing price on end day.

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

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

Anonymous — Wed, 24 Jul 2019 15:14:35 GMT

You could do these as measures, all depends on what your final goal is and all of that. But going the calculated column route:

Prev Close = 
var __CurrentDate= 'Table'[DateCounterID]
var __CurrentTicker = 'Table'[TickerID]
return

CALCULATE(
    sum( 'Table'[ClosePrice] ),
    filter(
        'Table',
        'Table'[TickerID] = __CurrentTicker
        && 
        'Table'[DateCounterID] = __CurrentDate - 1 
    )
)

3 Day Average = 
var __CurrentDate= 'Table'[DateCounterID]
var __CurrentTicker = 'Table'[TickerID]
vAR __MALength= 3
return

Var __MovingAverage=
CALCULATE(
    AVERAGEX(
    filter(
        ALL('Table'),
        'Table'[TickerID] = __CurrentTicker
        && 
        'Table'[DateCounterID] = __CurrentDate 
        &&
        'Table'[DateCounterID] >= __CurrentDate - (__MALength-1)
    ),
    sum('Table'[ClosePrice])
))

Var __RowCount=
COUNTROWS(
       filter(
        ALL('Table'),
        'Table'[TickerID] = __CurrentTicker
        && 
        'Table'[DateCounterID] <= __CurrentDate 
        
    )
)
RETURN
if( __RowCount >= __MALength,__MovingAverage)

Moving average one looks much worse than it is. Just basically feeding the averagex function a list of dates ( using your DateCounter ID, which for ticker1 skips from 10758 to 10760...) then does a check to see if there is enough dates for the moving average. Probably didnt want to see a 3 day MA figure on day two... You can just copy that code, change the __MALength variable to whatever other length.

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

Anonymous — Wed, 24 Jul 2019 15:16:27 GMT

Here's a post I did about moving averages in measure form.

https://community.powerbi.com/t5/Desktop/Moving-Average-Calculation/m-p/659153