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.
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,
Solved! Go to Solution.
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.
datemtd, datesqtd and datesytd , you can use . By changing the number in dateadd, you can go in past as much as you want
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD Sales 5 year back = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-5,YEAR)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-12,MONTH))))
MTD (Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date Filer])))
MTD (Last Year End) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date Filer],-12,MONTH),"8/31")))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date Filer])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],1,QUARTER)))
Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,Year)))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date Filer])))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-1,Year)))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hi @ashamsuzzoha ,
look at this.
https://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question
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.
Thanks for the reply, @mwegener.
I think I asked my question wrong. I would like the average of the previous 5 years, not just the value from 5 years ago. Is there a function that can do that?
Hi @ashamsuzzoha ,
is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @mwegener ,
My problem is still not solved. I tried the code you gave to try out and this is what I got:
Date Number of Leaks Number of Leaks Previous Year Average Number of Leak Previous 5 Years
1/1/2010 0:00 118 118
2/1/2010 0:00 56 56
3/1/2010 0:00 95 95
4/1/2010 0:00 123 123
5/1/2010 0:00 257 257
6/1/2010 0:00 185 185
7/1/2010 0:00 64 64
8/1/2010 0:00 37 37
9/1/2010 0:00 26 26
10/1/2010 0:00 0 0
11/1/2010 0:00 43 43
12/1/2010 0:00 14 14
1/1/2011 0:00 52 118 52
2/1/2011 0:00 102 56 102
3/1/2011 0:00 21 95 21
4/1/2011 0:00 71 123 71
5/1/2011 0:00 126 257 126
6/1/2011 0:00 143 185 143
7/1/2011 0:00 38 64 38
8/1/2011 0:00 104 37 104
9/1/2011 0:00 176 26 176
10/1/2011 0:00 286 0 286
11/1/2011 0:00 155 43 155
12/1/2011 0:00 246 14 246
1/1/2012 0:00 176 52 176
2/1/2012 0:00 194 102 194
3/1/2012 0:00 114 21 114
4/1/2012 0:00 86 71 86
5/1/2012 0:00 122 126 122
6/1/2012 0:00 104 143 104
7/1/2012 0:00 116 38 116
8/1/2012 0:00 68 104 68
9/1/2012 0:00 51 176 51
10/1/2012 0:00 232 286 232
11/1/2012 0:00 191 155 191
12/1/2012 0:00 162 246 162
1/1/2013 0:00 120 176 120
2/1/2013 0:00 60 194 60
3/1/2013 0:00 59 114 59
4/1/2013 0:00 50 86 50
5/1/2013 0:00 168 122 168
6/1/2013 0:00 241 104 241
7/1/2013 0:00 511 116 511
8/1/2013 0:00 225 68 225
9/1/2013 0:00 110 51 110
10/1/2013 0:00 111 232 111
11/1/2013 0:00 116 191 116
12/1/2013 0:00 130 162 130
1/1/2014 0:00 445 120 445
2/1/2014 0:00 335 60 335
3/1/2014 0:00 191 59 191
4/1/2014 0:00 184 50 184
5/1/2014 0:00 91 168 91
6/1/2014 0:00 27 241 27
7/1/2014 0:00 89 511 89
8/1/2014 0:00 105 225 105
9/1/2014 0:00 80 110 80
10/1/2014 0:00 267 111 267
11/1/2014 0:00 135 116 135
12/1/2014 0:00 0 130 0
1/1/2015 0:00 148 445 148
2/1/2015 0:00 125 335 125
3/1/2015 0:00 69 191 69
4/1/2015 0:00 100 184 100
5/1/2015 0:00 175 91 175
6/1/2015 0:00 183 27 183
7/1/2015 0:00 162 89 162
8/1/2015 0:00 173 105 173
9/1/2015 0:00 46 80 46
10/1/2015 0:00 178 267 178
11/1/2015 0:00 127 135 127
12/1/2015 0:00 2 0 2
1/1/2016 0:00 135 148
2/1/2016 0:00 162 125
3/1/2016 0:00 111 69
4/1/2016 0:00 64 100
5/1/2016 0:00 114 175
6/1/2016 0:00 129 183
7/1/2016 0:00 117 162
8/1/2016 0:00 102 173
9/1/2016 0:00 90 46
10/1/2016 0:00 133 178
11/1/2016 0:00 36 127
12/1/2016 0:00 28 2
1/1/2017 0:00 144 135
2/1/2017 0:00 76 162
3/1/2017 0:00 41 111
4/1/2017 0:00 41 64
5/1/2017 0:00 35 114
6/1/2017 0:00 28 129
7/1/2017 0:00 61 117
8/1/2017 0:00 43 102
9/1/2017 0:00 68 90
10/1/2017 0:00 84 133
11/1/2017 0:00 106 36
12/1/2017 0:00 1 28
1/1/2018 0:00 84 144
2/1/2018 0:00 68 76
3/1/2018 0:00 68 41
4/1/2018 0:00 50 41
5/1/2018 0:00 29 35
6/1/2018 0:00 69 28
7/1/2018 0:00 91 61
8/1/2018 0:00 126 43
9/1/2018 0:00 95 68
10/1/2018 0:00 31 84
11/1/2018 0:00 0 106
12/1/2018 0:00 0 1
1/1/2019 0:00 0 84
2/1/2019 0:00 11 68
3/1/2019 0:00 46 68
4/1/2019 0:00 40 50
5/1/2019 0:00 11 29
6/1/2019 0:00 1 69
7/1/2019 0:00 6 91
8/1/2019 0:00 8 126
Hi @ashamsuzzoha ,
You may download my PBIX file from here.
Hope this helps.
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 |
Hi @ashamsuzzoha ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
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.
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.
Hi @ashamsuzzoha ,
We can create a measure using following dax to meet your requirement:
Number of Leank Previous N Years =
VAR LastNYearTable =
GENERATESERIES ( 1, [Last N Year Value], 1 )
RETURN
IF (
COUNTROWS ( DATEADD ( 'Table'[Date], - [Last N Year Value], YEAR ) ) = 0,
BLANK (),
AVERAGEX (
LastNYearTable,
VAR y = [Value]
VAR d =
DATEADD ( 'Table'[Date], - y, YEAR )
RETURN
CALCULATE ( SUM ( 'Table'[Number of Leaks] ), ALLSELECTED ( 'Table' ), d )
)
)
Best regards,
Hi @ashamsuzzoha ,
After create a What-If Parameter, we can use a measure to meet your requirement:
Number of Leank Previous N Years = CALCULATE(SUM('Table'[Number of Leaks]),DATEADD(SAMEPERIODLASTYEAR('Table'[Date]),1-[Last N Year Value],YEAR))
If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.
Best regards,
try this
Measure =
CALCULATE (
AVERAGE ( 'Table'[Value] ),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
&& DATEADD ( 'Calendar'[Date], 5, YEAR ) >= MAX ( 'Calendar'[Date] )
)
)
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.
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |