How to calculate % compared to same time in a particular year, instead of last year

[Anonymous]

Hi there, 

I've come across an issue that I hope someone might be able to help me with...

I have been recieving a new weekly dataset of search behaviours since 25-October-2020, I have also been given historical data for all CY2019. Therefore have been able to benchmark performance in 2020 against 2019, now that we're in 2021 I have a gap in the data from Jan 2020 - 25-October-2020 and cannot use the caculation of DATEADD('CalendarDomestic'[Date], -1, YEAR) or SAMEPERIODLASTYEAR. Instead I would like to continue to benchmark in 2019, is there a formular to establish same time in X Year or same time in 2019, meaning i can benchmark my weekly data against the same week in 2019? 

I also have a slicer that allows users to filter by release dates (weekly), therefore would like to use ONE calculation for the full dataset
Thank you in advance!

[amitchandak · Accepted solution]

@Anonymous , Try like

if( max('Date'[Year]) = 2021, calculate([weekly total global search], DATEADD('Date'[Date], -2, YEAR)) ,calculate([weekly total global search], DATEADD('Date'[Date], -1, YEAR) ))

[Anonymous]

@amitchandak  & @DataZoe thank you both for your solutions, they both worked!

[DataZoe]

@Anonymous An approach to try is put your measures in an if statement to see if it's from 2021, then go back an additonal year, otherwise keep the previous logic:

if(year(current date) = 2021, DATEADD('CalendarDomestic'[Date], -2, YEAR) ,DATEADD('CalendarDomestic'[Date], -1, YEAR) )

if(year(current date) = 2021,SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR('CalendarDomestic'[Date])), SAMEPERIODLASTYEAR())

you can simply nest the sameperiodlastyear in another sameperiodlastyear to get it to go back an additional year.

[Anonymous]

@DataZoe thanks for coming back so quick!
There was an error with the formula and it is not accepting it

[DataZoe · Accepted solution]

Hi @Anonymous, thank you @amitchandak too, as his method would also work if you had a year in your calendar table.  You can also try the measure like this:

PYW Total Global Searches =
VAR _year =
    YEAR ( MAX ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        [Weekly Total Global Searches],
        IF (
            _year = 2021,
            DATEADD ( 'Calendar'[Date], -2, YEAR ),
            DATEADD ( 'Calendar'[Date], -1, YEAR )
        )
    )

[amitchandak · Accepted solution]

@Anonymous , Try like

if( max('Date'[Year]) = 2021, calculate([weekly total global search], DATEADD('Date'[Date], -2, YEAR)) ,calculate([weekly total global search], DATEADD('Date'[Date], -1, YEAR) ))