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.
-
Calculate Latest Complete Week
a. Start by creating the helper column [Latest Complete Week]. We’ll use this column to compare against our [Order Date] column and filter our orders that did not happen in the last complete week
b. To calculate [Latest Complete Week] calculate the current start of the week and then ‘subtract’ a weekDateAdd("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()))