Same Period Last N Years

[ashamsuzzoha]

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,

[amitchandak]

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

[mwegener]

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.

[ashamsuzzoha]

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?

[mwegener]

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.

[ashamsuzzoha]

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	

[mwegener]

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.

[ashamsuzzoha]

@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

[v-lid-msft]

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,

[mwegener · Accepted solution]

Hi @ashamsuzzoha ,

check this out.

PBIX

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.

[ashamsuzzoha]

Hi @mwegener , this appears to be what I need. Thanks!

[amitchandak]

 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.

[v-lid-msft]

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,

[v-lid-msft]

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,

[mwegener]

Hi @ashamsuzzoha 

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.