Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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) ))
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |