SAMEPERIODLASTYEAR doesn't work as a variable

[SuddenClarity]

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?

[Clinical_Epi]

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

[FreemanZ · Accepted solution]

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.