Forum Discussion

SuddenClarity's avatar
SuddenClarity
Icon for Helper I rankHelper 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 r...
  • 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.

Clinical_Epi's avatar
Clinical_Epi
Icon for Advocate III rankAdvocate 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