Creating Custom Rolling Date Ranges
Creating Custom Date Buckets
Creating rolling date parts relies heavily on an anchor point, like the TODAY() function. From there, you can obtain the month number and generate relevant rolling date parts accordingly.
In order to demonstrate how these calculations work and because I am working with the static Superstore data set, I have used a date parameter called “@Today” to act as the TODAY() function, thereby setting it to whatever date I see fit for testing purposes.
One way to calculate date parts that are linked to the TODAY() function is to utilize the modulo function. Modulo is an underrated, underused, and often unknown function in mathematics that divides one number by another and outputs whatever is left over. So 10 % (modulo) 8 would be 2; 13 % 4 would be 1 and even 1 % 0 would be 1 because 1 still remains.
When there are patterns in the numbers, which is often the case when it comes to date parts, modulo can prove especially useful.
Rolling date parts rely on their relationship to the most current date in order to be calculated. As such, we are going to look at DATEDIFF(‘month’,[Order Date],[@Today]) % 12.
The first thing we are going to do is use this pattern to determine our rolling quarters. You might be wondering why we don’t use DATEDIFF(‘month’,[Order Date],[@Today]) % 3 instead of 12, given that quarters are in fact made up of 3-month groupings. The simple answer is that after hours of toiling over this, I actually found this method of using 12 instead of 3 to be a shorter calculation, at least overall, as it is used as the basis for calculating a lot of the other rolling date parts as well.
So how my approach works is that it takes the resulting pattern outlined above and then incorporates IF / ELSE statements to determine whether any dates in the data set fall between 0 and 2, 3 and 5, 6 and 8, or 9 and 11 in the modulo result pool. This then determines whether those months should be grouped to a starting point 3 months before the month of @Today, 6 months, 9 months, or 12 months, totaling four starting points or four quarters.
This is because the current or latest month will always be the third month in a rolling quarter, so any months that share the same modulo result or range of results will also be the last month in their respective quarters, and so they would be grouped to a starting point of 3 months before @Today’s date.
Therefore, we could use something similar to the logic below. Notice that I use MAKEDATE() to create a completely new date that links to the year of the [Order Date] but relies on the MONTH() and DAY() of @Today in order to utilize DATEADD() correctly by linking the output to @Today’s date while keeping the year of the [Order Date] in mind. I also add an additional day to the overall date because we want periods to run from, for example, 2 January to 1 April and not from 1 January to 1 April.
Unfortunately, it’s not as simple as that, and things get a lot more complex as you continue to build. For example, if we rely on using the YEAR of [Order Date] for our MAKEDATE() calculation, what happens when a quarter spans across two years? If @Today falls on any date in January 2021, then while January itself may fall under 2021, November and December would fall under 2020.
What’s more, you’ll find that we also need to take into account the fact that any months that share the same modulo result with @Today’s date will need to be split into two separate quarters because rolling date parts are linked to a daily level. This means that any dates in that month where DAY([Order Date]) is less than or equal to DAY([@Today]) will need to be placed into the preceding rolling quarter, and any dates in that month where DAY([Order Date]) is greater than DAY([@Today]) will remain in their current assigned quarter.
That doesn’t seem too complex, but then you also need to consider that if DAY(@Today) is 30 or 31, then some quarters are going to roll into the 1st of the next month, which means your calculations need to account for that as well. Additionally, any selection of DAY(@Today)] that is greater than or equal to 28 will result in any rolling quarters falling in February to roll over to March due to February being a shorter month. Whether that number is 28 or 29 will depend on whether it is a leap year or not.
There are even a few more complex scenarios that I won’t bog you down with. The fact is, I’ve covered them all in my calculations. They are all pretty intense, so I’m not going to break them all down in too much detail. You are welcome to dissect them piece by piece in your own time to see how each section fits together to achieve our ultimate goal of rolling date parts.
But trust me when I say to you that as lengthy as they appear now, they would be 10 times longer without the modulo function, given that your alternative is a hornet’s nest of IF / ELSE statements. As odd as it may seem, I really did try to find the shortest solution possible. If you saw what I started with, you’d be impressed.
That said, if some legit genius out there figures out a way I could have made this work without the calculation breaking no matter the selection of @Today, I would gladly welcome that advice! But every time I thought I found a simpler solution, a few selections of @Today would prove that it would fail in at least a few places, especially when it came to 30-day months and February, in particular.
“DP: Rolling Quarters” Code:
DATE(
IF (DAY([@Today]) = 29 OR DAY([@Today]) = 30 OR DAY([@Today]) = 31)
AND (DATEDIFF('month',[Order Date],[@Today]) % 12 = 3
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 6
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 9
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) <= DAY([@Today])
THEN
IF DAY(MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-3,[Order Date])),DAY([@Today]))) = 1
THEN MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-3,[Order Date])),DAY([@Today]))
ELSEIF DAY(MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-3,[Order Date])),DAY([@Today]))) <= 4
THEN DATETRUNC('month',MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-3,[Order Date])),DAY([@Today])))
ELSE MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-3,[Order Date])),DAY([@Today])+1)
END
ELSEIF (DATEDIFF('month',[Order Date],[@Today]) % 12 = 3
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 6
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 9
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) <= DAY([@Today])
THEN MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-3,[Order Date])),DAY([@Today])+1)
ELSEIF (DATEDIFF('month',[Order Date],[@Today]) % 12 = 3
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 6
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 9
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) > DAY([@Today])
THEN MAKEDATE(YEAR([Order Date]),MONTH([Order Date]),DAY([@Today])+1)
ELSEIF DAY([@Today]) >= 28 AND { FIXED (DATEPART('year', [Order Date])*100
+ DATEPART('month', [Order Date])) : MAX(DAY(
IF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 2
THEN DATEADD('day',1,DATEADD('month',-3,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 5
THEN DATEADD('day',1,DATEADD('month',-6,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 8
THEN DATEADD('day',1,DATEADD('month',-9,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
))} <= 4
THEN DATETRUNC('month',
IF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 2
THEN DATEADD('day',1,DATEADD('month',-3,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 5
THEN DATEADD('day',1,DATEADD('month',-6,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 8
THEN DATEADD('day',1,DATEADD('month',-9,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
)
ELSE
IF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 2
THEN DATEADD('day',1,DATEADD('month',-3,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 5
THEN DATEADD('day',1,DATEADD('month',-6,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 8
THEN DATEADD('day',1,DATEADD('month',-9,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
END
)
Given that we’re dealing with multiple rolling date parts, including days, weeks, two weeks, months, quarters, thirds, half-years, and years, it’s best to create separate calculations for each rolling date part. We will then use an integer-based parameter to cycle between these different date parts.
Mine is called “@DateParts” and by double-clicking under the “Display As” column, I can set the aliases for these numbers to reflect the different date parts associated with them. Not many people know you can do this with integer-based parameters, but it makes your life much easier as you tie all calculations to a number instead of the alias string. This means you can change the alias as many times as you like without having to go back and update all the calculations linked to it.
As we’ve already covered the logic for rolling quarters, we’ll cover the remaining calculations below:
“DP: Rolling Weeks” Code:
IF DATEDIFF('day',[Order Date],[@Today]) % 7 = 0
THEN DATEADD('day',-6,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 7 = 1
THEN DATEADD('day',-5,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 7 = 2
THEN DATEADD('day',-4,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 7 = 3
THEN DATEADD('day',-3,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 7 = 4
THEN DATEADD('day',-2,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 7 = 5
THEN DATEADD('day',-1,[Order Date])
END
“DP: Rolling Two Weeks” Code:
IF DATEDIFF('day',[Order Date],[@Today]) % 14 = 0
THEN DATEADD('day',-13,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 1
THEN DATEADD('day',-12,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 2
THEN DATEADD('day',-11,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 3
THEN DATEADD('day',-10,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 4
THEN DATEADD('day',-9,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 5
THEN DATEADD('day',-8,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 6
THEN DATEADD('day',-7,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 7
THEN DATEADD('day',-6,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 8
THEN DATEADD('day',-5,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 9
THEN DATEADD('day',-4,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 10
THEN DATEADD('day',-3,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 11
THEN DATEADD('day',-2,[Order Date])
ELSEIF DATEDIFF('day',[Order Date],[@Today]) % 14 = 12
THEN DATEADD('day',-1,[Order Date])
END
“DP: Rolling Half Months” Code:
DATE(
IF DAY(
IF DAY([Order Date]) > DAY([@Today])
THEN MAKEDATE(YEAR([Order Date]),MONTH([Order Date]),DAY([@Today])+1)
ELSEIF DAY([Order Date]) <= DAY([@Today])
THEN MAKEDATE(YEAR(DATEADD('month',-1,[Order Date])),MONTH(DATEADD('month',-1,[Order Date])),DAY([@Today])+1)
END)
<= 4
THEN DATETRUNC('month',
IF DAY([Order Date]) > DAY([@Today])
THEN MAKEDATE(YEAR([Order Date]),MONTH([Order Date]),DAY([@Today])+1)
ELSEIF DAY([Order Date]) <= DAY([@Today])
THEN MAKEDATE(YEAR(DATEADD('month',-1,[Order Date])),MONTH(DATEADD('month',-1,[Order Date])),DAY([@Today])+1)
END
)
ELSE
IF DAY([Order Date]) > DAY([@Today])
THEN MAKEDATE(YEAR([Order Date]),MONTH([Order Date]),DAY([@Today])+1)
ELSEIF DAY([Order Date]) <= DAY([@Today])
THEN MAKEDATE(YEAR(DATEADD('month',-1,[Order Date])),MONTH(DATEADD('month',-1,[Order Date])),DAY([@Today])+1)
END
END
)
“DP: Rolling Thirds” Code:
DATE(IF (DAY([@Today]) = 29 OR DAY([@Today]) = 30 OR DAY([@Today]) = 31)
AND (DATEDIFF('month',[Order Date],[@Today]) % 12 = 4
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 8
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) <= DAY([@Today])
THEN
IF DAY(MAKEDATE(YEAR(DATEADD('month',-4,[Order Date])),
MONTH(DATEADD('month',-4,[Order Date])),DAY([@Today]))) = 1
THEN MAKEDATE(YEAR(DATEADD('month',-4,[Order Date])),
MONTH(DATEADD('month',-4,[Order Date])),DAY([@Today]))
ELSEIF DAY(MAKEDATE(YEAR(DATEADD('month',-4,[Order Date])),
MONTH(DATEADD('month',-4,[Order Date])),DAY([@Today]))) <= 4
THEN DATETRUNC('month',MAKEDATE(YEAR(DATEADD('month',-3,[Order Date])),
MONTH(DATEADD('month',-4,[Order Date])),DAY([@Today])))
ELSE MAKEDATE(YEAR(DATEADD('month',-4,[Order Date])),
MONTH(DATEADD('month',-4,[Order Date])),DAY([@Today])+1)
END
ELSEIF (DATEDIFF('month',[Order Date],[@Today]) % 12 = 4
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 4
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 8
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) <= DAY([@Today])
THEN MAKEDATE(YEAR(DATEADD('month',-4,[Order Date])),
MONTH(DATEADD('month',-4,[Order Date])),DAY([@Today])+1)
ELSEIF (DATEDIFF('month',[Order Date],[@Today]) % 12 = 4
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 4
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 8
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) > DAY([@Today])
THEN MAKEDATE(YEAR([Order Date]),MONTH([Order Date]),DAY([@Today])+1)
ELSEIF DAY([@Today]) >= 28 AND { FIXED (DATEPART('year', [Order Date])*100
+ DATEPART('month', [Order Date])) : MAX(DAY(
IF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 3
THEN DATEADD('day',1,DATEADD('month',-4,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 7
THEN DATEADD('day',1,DATEADD('month',-8,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
))} <= 3
THEN DATETRUNC('month',
IF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 3
THEN DATEADD('day',1,DATEADD('month',-4,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 7
THEN DATEADD('day',1,DATEADD('month',-8,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
)
ELSE
IF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 3
THEN DATEADD('day',1,DATEADD('month',-4,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSEIF DATEDIFF('month',[Order Date],[@Today]) % 12 <= 7
THEN DATEADD('day',1,DATEADD('month',-8,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today])
- MONTH([Order Date]) < 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
END
)
“DP: Rolling Half Years” Code:
DATE(
IF (DATEDIFF('month',[Order Date],[@Today]) % 12 = 6
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) <= DAY([@Today])
THEN MAKEDATE(YEAR(DATEADD('month',-6,[Order Date])),
MONTH(DATEADD('month',-6,[Order Date])),DAY([@Today])+1)
ELSEIF (DATEDIFF('month',[Order Date],[@Today]) % 12 = 6
OR DATEDIFF('month',[Order Date],[@Today]) % 12 = 0)
AND DAY([Order Date]) > DAY([@Today])
THEN MAKEDATE(YEAR([Order Date]),MONTH([Order Date]),DAY([@Today])+1)
ELSEIF { FIXED (DATEPART('year', [Order Date])*100
+ DATEPART('month', [Order Date])) : MAX(DAY(
IF DATEDIFF('month',[Order Date],[@Today]) % 12 < 6
THEN DATEADD('day',1,DATEADD('month',-6,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
))} <= 3
THEN DATETRUNC('month',
IF DATEDIFF('month',[Order Date],[@Today]) % 12 < 6
THEN DATEADD('day',1,DATEADD('month',-6,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
)
ELSE
IF DATEDIFF('month',[Order Date],[@Today]) % 12 < 6
THEN DATEADD('day',1,DATEADD('month',-6,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
ELSE DATEADD('day',1,DATEADD('month',-12,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today]))))
END
END
)
“DP: Rolling Years” Code:
DATE(
DATEADD('year',-1,MAKEDATE(YEAR(DATEADD('year',IF MONTH([@Today]) - MONTH([Order Date])
< 0 THEN 1 ELSE 0 END,[Order Date])),MONTH([@Today]),DAY([@Today])+1))
)
Fortunately, the logic for rolling “to date” calculations is much simpler! The only difference here is that you will be utilizing this calculation as a filter. Just don’t forget to utilize TODAY() in your calculations and not @Today, as I’ve done with mine. You do not need to add the filter to context as I have done.
“Filter: Rolling To Date” logic:
CASE [@DateParts]
//days
WHEN 1 THEN [Order Date] = [@Today]
//weeks
WHEN 2 THEN [Order Date] > DATEADD('day',-7,[@Today])
//two weeks
WHEN 3 THEN [Order Date] > DATEADD('day',-14,[@Today])
//months
WHEN 4 THEN [Order Date] > DATEADD('month',-1,[@Today])
//quarters
WHEN 5 THEN [Order Date] > DATEADD('quarter',-1,[@Today])
//thirds
WHEN 6 THEN [Order Date] > DATEADD('month',-4,[@Today])
//half years
WHEN 7 THEN [Order Date] > DATEADD('month',-6,[@Today])
//years
WHEN 8 THEN [Order Date] > DATEADD('year',-1,[@Today])
END
If you’d like to work with custom date parts that run from the start of each date part to the end and do not roll up to the latest day, you can view our post on Creating Custom Date Ranges and “To Date” Filters.
