Unexpected behaviour of Calculate with date filter

[MCarmassi]

In my dataset I have the following data model. The relation between Header and Detail is through IdPurchaseInvoiceHeader while the relation between 'Tabella Date' and Header is between InvoiceDate and Date. 'Tabella Date' is defined as 

Tabella Date = distinct(SELECTCOLUMNS(PurchaseInvoiceHeader,"Date",PurchaseInvoiceHeader[InvoiceDate],"Year",[Year],"MonthNumber",[MonthNumber],"Month",[Month]))

Now in my report I have a slicer on 'Tabella Date'[Year] and a line chart where I want to show PurchaseInvoiceDetail[NetAmount] of the chosen year and the previous year by Month (which is 'Tabella Date'[Month]). I have a problem with the previous year.

I tried defining the measure as

@ Amount Previous Year = 
var Anno=[@ Previous Year]
return
if(Anno<>Selectedvalue(Param[MaxYear])-1,calculate(sum(PurchaseInvoiceDetail[NetAmount]),'Tabella Date'[Year]=Anno),calculate(sum(PurchaseInvoiceDetail[NetAmount],'Tabella Date'[Date]<=Date(Anno,Month(selectedvalue(Param[value])),Day(Selectedvalue(Param[Value]))) && 'Tabella Date'[Year]=Anno))

Here Param is a calculated table with a single row with value max('Tabella Date'[InvoiceDate]) and [@ Previous Year] is a measure that gives the year before the selected one. Now, if I choose the most recent year the result of this measure is wrong, giving for every month the total of the year instead of the amount for the specific month. I tried adding KEEPFILTERS and/or various ALL and ALLEXCEPT, but nothing changed.

If instead I chose an year other than the most recent one (so the if goes to the first calculate instead of the second one) everything works fine.

My interpretation is that the external context gives a filter on Year and Month. In calculate the filter on Year is overwritten by the new year, so we should have the intersection fo the filters on Month, on the Date and on the year, but this isn't how it works, it seems that the filter on Month is also overwritten. 

[parry2k · Accepted solution]

@MCarmassi what @lbendlin said, you can check related videos here on my channel:

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

[lbendlin]

That's not how you define date tables. Your calendar table has to be both contiguous (no gaps) and covering, including the result of the time intelligence function.

Use CALENDAR() or (worst case) CALENDARAUTO() for that.

[MCarmassi]

Thank you. I corrected the calendar table to be contigous (using CALENDAR). I also understand now that adding a filter on the date of a calendar table removes all the other filter, so that is the reason I get the unexpected result.  

With a well defined calendar table I solved this specific issue using SAMEPERIODLASTYEAR,but I was wondering if is there a way to avoid this behaviour?

[lbendlin]

You want to let the data model do the work for you.  Only use filters if necessary.