Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
Here's a post I did about moving averages in measure form.
https://community.powerbi.com/t5/Desktop/Moving-Average-Calculation/m-p/659153
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |