Forum Discussion
same period last year using SAMEPERIODLASTYEAR return nothing
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/
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.
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/