Hi,
I've had a good look and there doesn't seem to be a simple fix to this!
I need to compare the last 4 weeks vs. the same 4 weeks last year.
So weeks 17-21 of 2018 vs. weeks 17-21 of 2017, for example.
I need this to be rolling (so as to avoid needing to manually update the filter at the start of each week)
So at the beginning of the next week it would use a relative date to recognise that it's now week 22 and to compare weeks 18-22.
I hope that makes sense!
It would be amazing if I could then have a drop down to select last week, last 4 weeks, and then YTD to compare vs. last year!! That would be amazing.
Thanks,
Jordan
Hi Jordan Brogden,
I *think* I've got it, I'm now using a different data source so that I do in fact have the correct dates for 2017!
So I've created a calculation to work out the ISO week:
INT((Datepart('dayofyear',
(dateadd('day',INT(datediff('day', ⌗1900-1-01#,
[Dates])/7)*7 + 3,⌗1900-1-01#))) + 6)/7)
Now I've used this and updated the calculation which we used for the filter:
if [Choose Period] = 1
then (DATEPART('week',TODAY())) = [iso]+1
and DATEPART('year',TODAY())= DATEPART('year',[Date])
OR
(DATEPART('week',TODAY())) = [iso]+1
and year(DATEADD( 'year',1,([Date]))) = YEAR(TODAY())
elseif [Choose Period] = 2
then (DATEPART('week',TODAY())) <
[iso]+5
and DATEPART('year',TODAY())= DATEPART('year',[Date])
and
(DATEPART('week',TODAY())) >
[iso]
OR
(DATEPART('week',TODAY())) <
[iso]+5
and
(DATEPART('week',TODAY())) >
[iso]
and year(DATEADD( 'year',1,([Date]))) = YEAR(TODAY())
else
[iso]+1<= DATEPART('week',TODAY())
and DATEPART('year', [Date]) = DATEPART('year', TODAY())
OR
[iso]+1
<= DATEPART('week',TODAY())
and year(DATEADD( 'year',1,([Date]))) = YEAR(TODAY())
END
Attached the workbook so that you can have a look. I really enjoy your questions, they're always really interesting and makes me think!
Thanks,
Mavis
