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

[sirgseymour]

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[Premium])

and then have created a second measure to output last year's data which is:

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

Unfortunately the output for the first measure is the same as the output for the second measure: it appears that the Premium LY is not being filtered by the SAMEPERIODLASTYEAR function. All the data is in one table. Any ideas?

Thanks

[Anonymous]

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]

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 · Accepted solution]

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]

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