Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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!
Solved! Go to 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) ))
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 )
)
)
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 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/
@DataZoe thanks for coming back so quick!
There was an error with the formula and it is not accepting it
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 )
)
)
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) ))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
206 | |
90 | |
62 | |
59 | |
57 |