Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

same period last year using SAMEPERIODLASTYEAR return nothing

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?

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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.