Forum Discussion
Conditional Simple Moving Average
Thank you Aniya for your reply. I tested your code and learning from it. The output is good, but it remains as slow as before. I also have tried Table.Buffer, also didn't help sofar. Please see attached a data sample of 3 tickers and 10 days of data (i am normally testing with 500 days of data which already makes it very very slow). Eventually I am using close to 10 years of data, and over 500 tickers.
ticker | date | open | high | low | close | volume | dividend | split | adj_open | adj_high | adj_low | adj_close | adj_volume |
A | 2020-01-02 | 85.9 | 86.35 | 85.2 | 85.95 | 1410486 | 0 | 1 | 84.08 | 84.52 | 83.39 | 84.13 | 1410486 |
A | 2020-01-03 | 84.67 | 85.33 | 84.5 | 84.57 | 1118322 | 0 | 1 | 82.87 | 83.52 | 82.71 | 82.78 | 1118322 |
A | 2020-01-06 | 84 | 84.82 | 83.6 | 84.82 | 1992491 | 0 | 1 | 82.22 | 83.02 | 81.83 | 83.02 | 1992491 |
A | 2020-01-07 | 83.96 | 85.26 | 83.94 | 85.08 | 1722896 | 0 | 1 | 82.18 | 83.45 | 82.16 | 83.28 | 1722896 |
A | 2020-01-08 | 85.96 | 86.47 | 85.2 | 85.92 | 1847585 | 0 | 1 | 84.14 | 84.64 | 83.39 | 84.1 | 1847585 |
A | 2020-01-09 | 86.46 | 87.7 | 86.17 | 87.27 | 1912699 | 0 | 1 | 84.63 | 85.84 | 84.34 | 85.42 | 1912699 |
A | 2020-01-10 | 87.72 | 88.24 | 87.32 | 87.59 | 1417028 | 0 | 1 | 85.86 | 86.36 | 85.47 | 85.73 | 1417028 |
A | 2020-01-13 | 87.81 | 88.32 | 86.74 | 87.46 | 1630160 | 0 | 1 | 85.95 | 86.45 | 84.9 | 85.61 | 1630160 |
A | 2020-01-14 | 87.27 | 88.21 | 86.7 | 87.99 | 1675243 | 0 | 1 | 85.42 | 86.34 | 84.86 | 86.12 | 1675243 |
A | 2020-01-15 | 87.63 | 89.11 | 87.55 | 88.62 | 1630364 | 0 | 1 | 85.77 | 87.22 | 85.69 | 86.74 | 1630364 |
AAL | 2020-01-02 | 28.98 | 29.3 | 28.65 | 29.09 | 6275633 | 0 | 1 | 28.88 | 29.19 | 28.55 | 28.99 | 6275633 |
AAL | 2020-01-03 | 28.27 | 28.29 | 27.34 | 27.65 | 14020066 | 0 | 1 | 28.17 | 28.19 | 27.24 | 27.55 | 14020066 |
AAL | 2020-01-06 | 27.19 | 27.49 | 27.08 | 27.32 | 6008794 | 0 | 1 | 27.09 | 27.39 | 26.99 | 27.22 | 6008794 |
AAL | 2020-01-07 | 27.56 | 27.68 | 27.06 | 27.22 | 6197079 | 0 | 1 | 27.46 | 27.58 | 26.97 | 27.12 | 6197079 |
AAL | 2020-01-08 | 27.1 | 28.09 | 27.07 | 27.84 | 10497296 | 0 | 1 | 27.01 | 27.99 | 26.98 | 27.74 | 10497296 |
AAL | 2020-01-09 | 28.09 | 28.23 | 27.71 | 27.95 | 6870153 | 0 | 1 | 27.99 | 28.13 | 27.61 | 27.85 | 6870153 |
AAL | 2020-01-10 | 27.96 | 27.99 | 27.25 | 27.32 | 8108419 | 0 | 1 | 27.86 | 27.89 | 27.15 | 27.22 | 8108419 |
AAL | 2020-01-13 | 27.32 | 27.53 | 27.16 | 27.39 | 5841463 | 0 | 1 | 27.22 | 27.43 | 27.06 | 27.29 | 5841463 |
AAL | 2020-01-14 | 27.91 | 28.4 | 27.35 | 27.53 | 8890948 | 0 | 1 | 27.81 | 28.3 | 27.25 | 27.43 | 8890948 |
AAL | 2020-01-15 | 27.45 | 28.06 | 27.32 | 27.58 | 5689926 | 0 | 1 | 27.35 | 27.96 | 27.22 | 27.48 | 5689926 |
AAP | 2020-01-02 | 160.47 | 160.95 | 157.53 | 159.41 | 944940 | 0 | 1 | 149.49 | 149.94 | 146.75 | 148.5 | 944940 |
AAP | 2020-01-03 | 158.03 | 159.63 | 157.25 | 159.42 | 566975 | 0 | 1 | 147.22 | 148.71 | 146.49 | 148.51 | 566975 |
AAP | 2020-01-06 | 157.74 | 158.89 | 156.43 | 156.8 | 879926 | 0 | 1 | 146.95 | 148.02 | 145.73 | 146.07 | 879926 |
AAP | 2020-01-07 | 156.9 | 157.4 | 152.41 | 154.94 | 1132189 | 0 | 1 | 146.17 | 146.63 | 141.98 | 144.34 | 1132189 |
AAP | 2020-01-08 | 154.85 | 156.05 | 153.14 | 153.16 | 825747 | 0 | 1 | 144.26 | 145.37 | 142.66 | 142.68 | 825747 |
AAP | 2020-01-09 | 153.78 | 153.98 | 150.25 | 152.87 | 1177403 | 0 | 1 | 143.26 | 143.45 | 139.97 | 142.41 | 1177403 |
AAP | 2020-01-10 | 153.18 | 153.5 | 148.61 | 149.01 | 1416121 | 0 | 1 | 142.7 | 143 | 138.44 | 138.82 | 1416121 |
AAP | 2020-01-13 | 145.33 | 147 | 142.76 | 145.26 | 2138699 | 0 | 1 | 135.39 | 136.94 | 132.99 | 135.32 | 2138699 |
AAP | 2020-01-14 | 145.59 | 149.08 | 144.34 | 148.9 | 1568390 | 0 | 1 | 135.63 | 138.88 | 134.47 | 138.71 | 1568390 |
AAP | 2020-01-15 | 149.58 | 151.49 | 147.82 | 150.21 | 1208806 | 0 | 1 | 139.35 | 141.13 | 137.71 | 139.93 | 1208806 |
Community SupportHi , Jan-Jaap72
Thanks for your quick response!
According to your description, your current loading speed is still very slow. I'm so sorry, I've tried my best to help you reduce the steps in Power Query. You can try dropping unneeded columns before calculating to see if that improves performance.
And also you can try to use the dax to replace the M code .
Column 2 = RANKX( FILTER('Query1','Query1'[ticker]=EARLIER(Query1[ticker])) , CALCULATE( MAX('Query1'[date]),ALLEXCEPT('Query1','Query1'[date]) ) , [date],ASC,Dense)
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly