topic Re: SAMEPERIODLASTYEAR doesn't work as a variable in DAX Commands and Tips

SAMEPERIODLASTYEAR doesn't work as a variable

SuddenClarity — Thu, 19 Jan 2023 20:47:32 GMT

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?

Re: SAMEPERIODLASTYEAR doesn't work as a variable

FreemanZ — Thu, 19 Jan 2023 22:03:19 GMT

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.

Re: SAMEPERIODLASTYEAR doesn't work as a variable

Clinical_Epi — Thu, 19 Jan 2023 22:24:08 GMT

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