Forum Discussion
Solved
Same Period Last N Years
Is there a function that resembles SAMEPERIODLASTYEAR but that can be expanded to more than one year back? Like the average of a monthly value for the same month the last five years? Thanks,
Hi ashamsuzzoha ,
check this out.
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
v-lid-msft mwegener amitchandak Thanks for the help. That is not exactly what I'm looking for. I am wanting the monthly average of the previous 5 years. Since the data starts in 2010, obviously this measure cannot start until 2015. So for Jan 2015, I want the average of Jan 2010, Jan 2011, Jan 2012, Jan 2013 and Jan 2014. Here is a sample output of what I want.
| Date | Number of Leaks | Number of Leaks Previous Year | Average Number of Leak Previous 5 Years |
| 1/1/2010 0:00 | 118 | ||
| 2/1/2010 0:00 | 56 | ||
| 3/1/2010 0:00 | 95 | ||
| 4/1/2010 0:00 | 123 | ||
| 5/1/2010 0:00 | 257 | ||
| 6/1/2010 0:00 | 185 | ||
| 7/1/2010 0:00 | 64 | ||
| 8/1/2010 0:00 | 37 | ||
| 9/1/2010 0:00 | 26 | ||
| 10/1/2010 0:00 | 0 | ||
| 11/1/2010 0:00 | 43 | ||
| 12/1/2010 0:00 | 14 | ||
| 1/1/2011 0:00 | 52 | 118 | |
| 2/1/2011 0:00 | 102 | 56 | |
| 3/1/2011 0:00 | 21 | 95 | |
| 4/1/2011 0:00 | 71 | 123 | |
| 5/1/2011 0:00 | 126 | 257 | |
| 6/1/2011 0:00 | 143 | 185 | |
| 7/1/2011 0:00 | 38 | 64 | |
| 8/1/2011 0:00 | 104 | 37 | |
| 9/1/2011 0:00 | 176 | 26 | |
| 10/1/2011 0:00 | 286 | 0 | |
| 11/1/2011 0:00 | 155 | 43 | |
| 12/1/2011 0:00 | 246 | 14 | |
| 1/1/2012 0:00 | 176 | 52 | |
| 2/1/2012 0:00 | 194 | 102 | |
| 3/1/2012 0:00 | 114 | 21 | |
| 4/1/2012 0:00 | 86 | 71 | |
| 5/1/2012 0:00 | 122 | 126 | |
| 6/1/2012 0:00 | 104 | 143 | |
| 7/1/2012 0:00 | 116 | 38 | |
| 8/1/2012 0:00 | 68 | 104 | |
| 9/1/2012 0:00 | 51 | 176 | |
| 10/1/2012 0:00 | 232 | 286 | |
| 11/1/2012 0:00 | 191 | 155 | |
| 12/1/2012 0:00 | 162 | 246 | |
| 1/1/2013 0:00 | 120 | 176 | |
| 2/1/2013 0:00 | 60 | 194 | |
| 3/1/2013 0:00 | 59 | 114 | |
| 4/1/2013 0:00 | 50 | 86 | |
| 5/1/2013 0:00 | 168 | 122 | |
| 6/1/2013 0:00 | 241 | 104 | |
| 7/1/2013 0:00 | 511 | 116 | |
| 8/1/2013 0:00 | 225 | 68 | |
| 9/1/2013 0:00 | 110 | 51 | |
| 10/1/2013 0:00 | 111 | 232 | |
| 11/1/2013 0:00 | 116 | 191 | |
| 12/1/2013 0:00 | 130 | 162 | |
| 1/1/2014 0:00 | 445 | 120 | |
| 2/1/2014 0:00 | 335 | 60 | |
| 3/1/2014 0:00 | 191 | 59 | |
| 4/1/2014 0:00 | 184 | 50 | |
| 5/1/2014 0:00 | 91 | 168 | |
| 6/1/2014 0:00 | 27 | 241 | |
| 7/1/2014 0:00 | 89 | 511 | |
| 8/1/2014 0:00 | 105 | 225 | |
| 9/1/2014 0:00 | 80 | 110 | |
| 10/1/2014 0:00 | 267 | 111 | |
| 11/1/2014 0:00 | 135 | 116 | |
| 12/1/2014 0:00 | 0 | 130 | |
| 1/1/2015 0:00 | 148 | 445 | 182 |
| 2/1/2015 0:00 | 125 | 335 | 149 |
| 3/1/2015 0:00 | 69 | 191 | 96 |
| 4/1/2015 0:00 | 100 | 184 | 103 |
| 5/1/2015 0:00 | 175 | 91 | 153 |
| 6/1/2015 0:00 | 183 | 27 | 140 |
| 7/1/2015 0:00 | 162 | 89 | 164 |
| 8/1/2015 0:00 | 173 | 105 | 108 |
| 9/1/2015 0:00 | 46 | 80 | 89 |
| 10/1/2015 0:00 | 178 | 267 | 179 |
| 11/1/2015 0:00 | 127 | 135 | 128 |
| 12/1/2015 0:00 | 2 | 0 | 110 |
| 1/1/2016 0:00 | 135 | 148 | 188 |
| 2/1/2016 0:00 | 162 | 125 | 163 |
| 3/1/2016 0:00 | 111 | 69 | 91 |
| 4/1/2016 0:00 | 64 | 100 | 98 |
| 5/1/2016 0:00 | 114 | 175 | 136 |
| 6/1/2016 0:00 | 129 | 183 | 140 |
| 7/1/2016 0:00 | 117 | 162 | 183 |
| 8/1/2016 0:00 | 102 | 173 | 135 |
| 9/1/2016 0:00 | 90 | 46 | 93 |
| 10/1/2016 0:00 | 133 | 178 | 215 |
| 11/1/2016 0:00 | 36 | 127 | 145 |
| 12/1/2016 0:00 | 28 | 2 | 108 |
| 1/1/2017 0:00 | 144 | 135 | 205 |
| 2/1/2017 0:00 | 76 | 162 | 175 |
| 3/1/2017 0:00 | 41 | 111 | 109 |
| 4/1/2017 0:00 | 41 | 64 | 97 |
| 5/1/2017 0:00 | 35 | 114 | 134 |
| 6/1/2017 0:00 | 28 | 129 | 137 |
| 7/1/2017 0:00 | 61 | 117 | 199 |
| 8/1/2017 0:00 | 43 | 102 | 135 |
| 9/1/2017 0:00 | 68 | 90 | 75 |
| 10/1/2017 0:00 | 84 | 133 | 184 |
| 11/1/2017 0:00 | 106 | 36 | 121 |
| 12/1/2017 0:00 | 1 | 28 | 64 |
| 1/1/2018 0:00 | 84 | 144 | 198 |
| 2/1/2018 0:00 | 68 | 76 | 152 |
| 3/1/2018 0:00 | 68 | 41 | 94 |
| 4/1/2018 0:00 | 50 | 41 | 88 |
| 5/1/2018 0:00 | 29 | 35 | 117 |
| 6/1/2018 0:00 | 69 | 28 | 122 |
| 7/1/2018 0:00 | 91 | 61 | 188 |
| 8/1/2018 0:00 | 126 | 43 | 130 |
| 9/1/2018 0:00 | 95 | 68 | 79 |
| 10/1/2018 0:00 | 31 | 84 | 155 |
| 11/1/2018 0:00 | 0 | 106 | 104 |
| 12/1/2018 0:00 | 0 | 1 | 32 |
| 1/1/2019 0:00 | 0 | 84 | 191 |
| 2/1/2019 0:00 | 11 | 68 | 153 |
| 3/1/2019 0:00 | 46 | 68 | 96 |
| 4/1/2019 0:00 | 40 | 50 | 88 |
| 5/1/2019 0:00 | 11 | 29 | 89 |
| 6/1/2019 0:00 | 1 | 69 | 87 |
| 7/1/2019 0:00 | 6 | 91 | 104 |
| 8/1/2019 0:00 | 8 | 126 | 110 |
The quick one I can think of is
last 5 MTD Sales = ( CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,Year)))
+CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,Year)))
+CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-3,Year)))
+CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-4,Year)))
+CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-5,Year)))
)/5
Few Adjustments need to be done to take care of no of year. So instead of divide by 5 . We can take count distinct of years and divide.