Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

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

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!

2 ACCEPTED SOLUTIONS

@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) ))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

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], -2YEAR ),
            DATEADD ( 'Calendar'[Date], -1YEAR )
        )
    )

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

DataZoe
Microsoft Employee
Microsoft Employee

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

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Anonymous
Not applicable

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

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], -2YEAR ),
            DATEADD ( 'Calendar'[Date], -1YEAR )
        )
    )

 

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

@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) ))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.