I need to compare data from periods of 2 weeks each time. For example - week 10-17 OCT to week 17-24 OCT
I want to create a dashboard
When access the dashboard I want to choose each time a period of LAST FULL 2 WEEKS (Monday to Monday) regardless to today's date.
Tried to make a calculated filed that adds and reduces days according to current date with DATEPART. maybe there is a better way? because this did not work for me as well.
There is probably a better way to do this, but for the sake of a quick answer that works...
Create a calculated field with this logic
if (datepart('week',[Order Date])= (datepart('week',today())) -2)
or (datepart('week',[Order Date])= (datepart('week',today())) -1)
then 'Include'
else 'Exclude'
end
There are two things happening in this formula: first, we are retrieving the week number (1 through 52) from the date field ([order date]). Next we retrieve the week number for the current week and subtract 2 from the result. If the record ([Order Date]) is = to the current week minus 2, OR the current week minus 1, then "Include", otherwise exclude.
Then, you can add this field to the filter pane and set to only include 'Include'