same period last year using SAMEPERIODLASTYEAR return nothing

[Anonymous]

I have a date dimension table with a date key and a date field. the date key is joined to the date key in sales table

but the

Total Sales LY = CALCULATE(SUM('Sales'[Today sales]), SAMEPERIODLASTYEAR('Date'[Date]))

return nothing

but I have another similar model using joint with date field rather than the date, the functions works. I am not sure if the difference causes the issue? Any one got a clue?

[Anonymous]

Set up another simple model to test this. I was able to replicate the issue. 

If the date dimension is joined to a fact table with a date column (EG 2 may 2016), the sameperiodlastyear function (and the dateadd etc) will work.

If the date dimension is joined to a fact table with a datekey column (EG 20160502), the sameperiodlastyear function (and the dateadd etc) will not work.

Can anyone confirm this is a known issue with Power BI or something missing in my setup?

At the end of the day, as a workaround, I can always add a date column in the fact table. But it just does not make sense as the date dimension should use date key for joining. 

[Anonymous · Accepted solution]

OK. Looks like expert has confirmed my theory that we SHOULD not have surrogate key in the Date dimension for the function to work. 

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/