SAMEPERIODLASTYEAR and DATEADD return BLANK value from last year

[M001]

Hi All,

I'm stuck and need help. I have a simple table with just Date and Units as shown below. I need to create a column to return value from the same month in previous year in Power Pivot Data Model. Unfortunately, I have tried the following DAX formula but all return as Blank value,

=CALCULATE(SUM([Units]), SAMEPERIODLASTYEAR(Table1[Date]))

=CALCULATE(SUM([Units]), DATEADD(Table1[Date], -1, YEAR))

Alternatively, I have tried using PREVIOUSYEAR. There are numbers showing but it's incorrect and I cant figure it out why either

=CALCULATE(SUM([Units]), PREVIOUSYEAR(Table1[Date]))

Hope someone could enlighten me, thank you!!

[jmlesteves · Accepted solution]

Hi

Sorry for the first answer, I misunderstood your question and now I noticed you want a Calculated Column, instead of a Measure.
Try this:
CALCULATE (
    SUM ( Table1[Units] );
    ALL ( Table1 );
    SAMEPERIODLASTYEAR ( Table1[Date] )
)

[M001]

Thank you, I just realize it's a filter seting issue, not exactly the same as your solution but your help has definitely enlightened me and I appreciate a lot