topic Re: SAMEPERIODLASTYEAR and DATEADD return BLANK value from last year in DAX Commands and Tips

SAMEPERIODLASTYEAR and DATEADD return BLANK value from last year

M001 — Sun, 15 Oct 2023 18:50:58 GMT

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!!

Re: SAMEPERIODLASTYEAR and DATEADD return BLANK value from last year

jmlesteves — Sun, 15 Oct 2023 22:42:51 GMT

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] )
)

Re: SAMEPERIODLASTYEAR and DATEADD return BLANK value from last year

M001 — Mon, 16 Oct 2023 01:52:18 GMT

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