Forum Discussion

SuddenClarity's avatar
SuddenClarity
Helper I
3 years ago
Solved

SAMEPERIODLASTYEAR doesn't work as a variable

I tried to write the following:

 

Sales YoY =
VAR CY = [Total Sales]
VAR PY = CALCULATE(CY, SAMEPERIODLASTYEAR('Calendar'[Date]))

RETURN
IF ( ISBLANK (PY),
BLANK(),
CY - PY  )
 
But it returns 0. I then put the 'PY' variance in the return statement and it returns the CY measure, hence why its calculating 0. But when I simply write the measure CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])), it works.. 
 
Can you not use SAMEPERIODLASTYEAR in a variable?
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi SuddenClarity 

    the issue is from the nature of variable in DAX. Once defined, a variable is stored as a constant. So whatever filter you add in expressions like

    CALCULATE(CY, SAMEPERIODLASTYEAR('Calendar'[Date]))

    it simply return CY, a constant defined before. 

    So CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])) is the right approach.

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi SuddenClarity 

    the issue is from the nature of variable in DAX. Once defined, a variable is stored as a constant. So whatever filter you add in expressions like

    CALCULATE(CY, SAMEPERIODLASTYEAR('Calendar'[Date]))

    it simply return CY, a constant defined before. 

    So CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])) is the right approach.

  • Clinical_Epi's avatar
    Clinical_Epi
    Advocate III
    3 years ago

    Dear Sudden Clarity,

     

    You could use same period last year as a Variable - see my example where as Variable is using same periodlastyear

     

    Test measure =
    VAR TY = TOTALYTD(COUNT('IP SQL Data'[URN]),_Date[Date filter])
    VAR LY = CALCULATE(COUNT('IP SQL Data'[URN]),SAMEPERIODLASTYEAR(_Date[Date filter]))
    RETURN TY / LY
     
    Hope this confirms with you it can be done 🙂

    and with date change,

     

    Kind regards,
    Clin Epi