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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi sirgseymour

I dont see any filer for This year Premium? Do you want to consider something like below for Premium TY?

 

Premium TY= CALCULATE(SUM(Sheet1[Premium]),FILTER(Sheet1,YEAR(Sheet1[Date])= YEAR(TODAY())

 

Try this formula for last year premium:

Premium LY = calculate([Premium TY],SAMEPERIODLASTYEAR(Sheet1[Date]))

 

If you have dedicated Date dimension table, try this:

Premium LY= CALCULATE(SUM(Sheet1[Premium]),DATEADD(Dates[Date],-1,YEAR)

 

Thanks

Raj

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

    Hi Raj,

     

    THe data is over a series of years (2014 -2018). What I am looking to compare the prior year data against the current year data for all years. The filtered Premium TY formula that you gave me below only produces the most current year data and I cannot get the Preimum LY to produce any data at all. So, for example if I look at 1/1/2015 for the Premium TY I should see the 2015 data and for the same period the prior year data should be from 2014. All I seem to be able to reproduce using the Premium LY formula is still the current year data.

     

    Thanks

    • 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..