Forum Discussion

sirgseymour's avatar
sirgseymour
Icon for Helper I rankHelper I
8 years ago
Solved

Sameperiodlastyear function is not working - returns current year's data

Hi,   I have some simple data I am trying to compare between this year and the prior year. THe data is in two columns: Date and Premium. I have created measure called:   Premium TY = sum(Sheet1[P...
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi sirgseymour,

     

    Sameperiodlastyear() will return a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. So the fucntion is based on current context which should not be used in your senario.

     

    As a workaround, create a measure using DAX like this pattern and check if it can meet your requirement:

    Result = 
VAR Previous_Year = DATE(YEAR(MAX(Table1[Date])) - 1,  MONTH(MAX(Table1[Date])), DAY(MAX(Table1[Date])))
RETURN
CALCULATE(MAX(Table1[Sales]), FILTER(ALL(Table1), Table1[Date] = Previous_Year))

      

     

    Regards,

    Jimmy Tao

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi sirgseymour,

 

Sameperiodlastyear() will return a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. So the fucntion is based on current context which should not be used in your senario.

 

As a workaround, create a measure using DAX like this pattern and check if it can meet your requirement:

Result = 
VAR Previous_Year = DATE(YEAR(MAX(Table1[Date])) - 1,  MONTH(MAX(Table1[Date])), DAY(MAX(Table1[Date])))
RETURN
CALCULATE(MAX(Table1[Sales]), FILTER(ALL(Table1), Table1[Date] = Previous_Year))

  

 

Regards,

Jimmy Tao

Anonymous's avatar
Anonymous
Not applicable
7 years ago

This solution seems to be showing a different number than the previous year's value..