Using DATEADD() or SAMEPERIODLASTYEAR() with a many-to-many relationship

[Anonymous]

Hi I am sorry I can not give upload my pbix file but I will try to be as clear as possible. 

So I have a fact table that contains many versions of a single between 2013-2020 and a date table that also contains many versions of a single year (I tried just having a date table with the 7 specific years, yes that removes the many to many relationship but I can not do DATEADD(Date[Year]) this function does not work. The error given is that DATEADD is not of type DATE).

I am trying to calculate the Total Value of my fact table. Doing a simple sum works and gives me the correct values however when I use 

Measure = CALCULATE([Total Value],SAMEPERIODLASTYEAR(Dates[Date])) I get a blank result.
What I am trying to do is get the same result but for the previous year only.

Note: This can be ignored just something I realised. When I Filter just one year it remains blank, however when I filter more than one year it seems to take all the dates before the most recent one. For example if I filter 2013,2014 & 2015. The measure above actually works for both years 2013 and 2014. 

I feel like I am missing a simple fundamental concept.

[v-xuding-msft]

Hi @Anonymous ,

Is the date table a calendar table?  if not, I think you need to create a calendar table firstly. The following is my sample using the both functions of DATEADD and  SAMEPERIODLASTYEAR  you can have a try.

1. I have two tables. The table Date is a calendar. And there is a many-to-one relationship between them.

Date = CALENDARAUTO()
year = YEAR('Date'[Date].[Date])

1. Create some measures.

SAMEPERIODLASTYEAR = CALCULATE(SUM(Table1[ Sales]),SAMEPERIODLASTYEAR('Date'[Date]))
DATEADD = CALCULATE(SUM(Table1[ Sales]),DATEADD('Date'[Date],-1,YEAR))

Note: If you choose 2015 to 2017, then it will be calculated the values of last year of the selected date. So the values of 2017 will not be showed.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.