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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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