Dynamically comparing current totals to last year totals

[vvcortazar]

Hi, I have a table that has data for each month for some years, I need to come up with a formula to add three months and then divided then to the same period of the previous year.

Jun 2016	150451
Jul 2016	548105
Aug 2016	712020	1410576
Sep 2016	181055
Oct 2016	481209
Nov 2016	181940
Dec 2016	246501
Jan 2017	257810
Feb 2017	180248
Mar 2017	293700
Apr 2017	213000
May 2017	241600
Jun 2017	270201
Jul 2017	252900
Aug 2017	152768	675869	0.47914398

Let me try to explain, I this example I need to sum the values for the period from June to August for 2017 and 2016 and then divide them. 

So far I made a measure that obtains the rolling sum of the three months and another to get the data for the previous year using the SAMEPERIODLASTYEAR formula but I get an error that the formula expects a contiguos selection when the date column is not unique. I can't figure the correct way to do this.

For the running total my measure is 

RunningTotal = var minDate= DATE(YEAR(MAX(Dates[Date])),MONTH(max(Dates[Date]))-2,DAY(mAX(Dates[Date])))
return
SUMX(FILTER(ALL('CAGR'),[CAGR_DATE]>= minDate&&[CAGR_DATE]<=MAX([CAGR_DATE])),[CAGR])

Any help is greatly appreciated.

[Ashish_Mathur]

Hi,

Here are the calculated field formulas that i wrote

Amount1

=CALCULATE(SUM(Data[Amount]),DATESYTD('calendar'[Date],"31/5"))

Amount in same period last year

=CALCULATE([Amount1],SAMEPERIODLASTYEAR('calendar'[Date]))

Achievement (%)

=[Amount1]/[Amount in same period last year]

[vvcortazar]

With the second formula I get the same message as before: 

MdxScript(Model) (12, 67) Calculation error in measure 'Data'[Amount in same period last year]: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time

[v-yulgu-msft · Accepted solution]

Hi @vvcortazar,

From the error message, please check whether Dates[Date] contains continual dates. If not, you need to create a separate calendar table use CALENDAR function, then relate your current table to the new date table.

Best regards,
Yuliana Gu

[vvcortazar]

Thanks for the reply, I created the Dates table and now everyting works fine.