Forum Discussion
Moving Average by Ranked Index
Dear Community,
I am trying to calculate the 13 Week Moving Average.
My data model is very simple.
Please see below for my current attempt and error message.
Greatly appreciate any and all help with this issue.
Thank you in advance!
Here is your code...
90 Day Average = VAR sDate = MAX('Table2'[Date]) VAR bDate = sDate-90 VAR sValue = CALCULATE(AVERAGE('Table2'[Count]),FILTER(ALL('Table2'[Date]),'Table2'[Date] >= bDate && 'Table2'[Date] <= sDate)) RETURN sValueChange the "Calculate(Average" to a Max or Min for the other two measures and get the MAX Date.
90 Min = VAR sDate = MAXX(ALL('Table2'[Date]),'Table2'[Date]) VAR bDate = sDate-90 VAR sValue = CALCULATE(MIN('Table2'[Count]),FILTER(ALL('Table2'[Date]),'Table2'[Date] >= bDate && 'Table2'[Date] <= sDate)) RETURN sValue
10 Replies
BrianConnellyResolver III
Change MyIndex to
VAR MyIndex = SELECTEDVALUE('Proposals'[Index],<A Default Value>)A default value can be something like MAX()
MAX('Proposals'[Index])Change your filter to
FILTER(ALL('Proposals')....Thank you so much for your quick response.
I have updated the code to the following:
Moving Average = VAR MyIndex = SELECTEDVALUE('Proposals'[Index],MAX('Proposals'[Index])) VAR myResult = AVERAGEX( FILTER(ALL( 'Proposals'), 'Proposals'[Index] > MyIndex-13 && 'Proposals'[Index] <= MyIndex ),'Proposals'[Proposals] ) RETURN FIXED(myResult,2)Unfortunately, the moving average column is all coming back as "0.00"
Is there an easy way to share my Power BI file?
- BrianConnelly
You have to upload to GIT or Onedrive and share Publicly the link. You could also past sample data and image of the dataset.
Please see below for sample data:
Date Proposals
1/3/2021 56 1/10/2021 101 1/17/2021 97 1/24/2021 145 1/31/2021 205 2/7/2021 125 2/14/2021 123 2/21/2021 143 2/28/2021 156 3/7/2021 127 3/14/2021 141 3/21/2021 132 3/28/2021 135 4/4/2021 162 4/11/2021 175 4/18/2021 167 4/25/2021 209 5/2/2021 182 5/9/2021 205 5/16/2021 154 5/23/2021 218 5/30/2021 158 6/6/2021 248 6/13/2021 140 6/20/2021 145 6/27/2021 112 7/4/2021 107 7/11/2021 168 7/18/2021 156 7/25/2021 146 8/1/2021 163 8/8/2021 161 8/15/2021 163 8/22/2021 196 8/29/2021 193 9/5/2021 156 9/12/2021 217 9/19/2021 262 9/26/2021 243 10/3/2021 217 10/10/2021 232 10/17/2021 267 10/24/2021 246 10/31/2021 191 11/7/2021 332 11/14/2021 262 11/21/2021 70 11/28/2021 197 12/5/2021 87 12/12/2021 73 12/19/2021 21 12/26/2021 7 1/2/2022 111 1/9/2022 219 1/16/2022 219 1/23/2022 210 1/30/2022 293 2/6/2022 261 2/13/2022 212 2/20/2022 148 2/27/2022 203 3/6/2022 225 3/13/2022 201 3/20/2022 184 3/27/2022 199 4/3/2022 155 4/10/2022 200 4/17/2022 235 4/24/2022 213 5/1/2022 286 5/8/2022 135 5/15/2022 198 5/22/2022 106 5/29/2022 134 6/5/2022 170 6/12/2022 221 6/19/2022 163 6/26/2022 121 7/3/2022 124 7/10/2022 149 7/17/2022 164 7/24/2022 136 7/31/2022 213 8/7/2022 209 8/14/2022 186 8/21/2022 208 8/28/2022 173 9/4/2022 141 9/11/2022 221 9/18/2022 266 9/25/2022 216 10/2/2022 279 10/9/2022 243 10/16/2022 240 10/23/2022 216 10/30/2022 230 11/6/2022 238 11/13/2022 287 11/20/2022 55 11/27/2022 285 12/4/2022 290 12/11/2022 141 12/18/2022 31 12/25/2022 12 1/2/2023 75 1/9/2023 116 1/16/2023 167 1/23/2023 196 1/30/2023 208 2/6/2023 183 2/13/2023 271 2/20/2023 196 2/27/2023 195 Does this link work for you to get to the Power BI file?
- grantsamborn
Solution Sage
Hi alexpetersen
Would something like this help?
My RA = VAR _Curr = SELECTEDVALUE( 'Proposals'[Index] ) VAR _Result = AVERAGEX( FILTER( ALL( 'Proposals' ), 'Proposals'[Index] <= _Curr && 'Proposals'[Index] >= _Curr - 12 ), 'Proposals'[Proposals] ) RETURN _Result
