We often want to calculate a total for the previous complete week, month etc. We’ll start out by showing how to calculate totals for the latest complete week, but at the end we’ll show how to make the time graining dynamic.
In our example, we have an [Order Date] and [Revenue] column and will calculate total revenue for the latest complete week.
b. To calculate [Latest Complete Week] calculate the current start of the week and then ‘subtract’ a week
DateAdd("week", -1, DateTrunc("week", Today()))
a. Now create a new column to filter out orders that did not occur in the last week. We’ll call this boolean column [Is Lastest Complete Week]
b. First calculate the order’s week using
DateTrunc("week", [Order Date]))
c. Then compare to [Is Latest Complete Week]
DateTrunc("week", [Order Date]) = [Lastest Complete Week]
a. Last thing we need to do is apply a filter to the [Is Latest Complete Week] column
b. From here calculate a table summary or build a visualization using the revenue column
a. We can use a parameter to make time graining dynamic allowing us to grain on day, week, month etc.
b. Click the plus sign on the top left and select List Values under Control Elements
c. Change Control Type to Parameter
d. Setup the parameter with the different time grainings. Make sure to make the parameter ‘required’ and that ‘Show Null option’ and ‘Allow multiple selection’ are unchecked
e. Now in your workbook formulas replace week with the control-id from your parameter e.g
DateAdd([time-graining], -1, DateTrunc([time-graining], Today()))