Sign in or start a free trial to avail of this feature.
5. Customizing Time Offsets
In this lesson, we will see how to make measures more responsive to the user's inputs, by creating a slicer that allows the user to customize the time offset for our measures from the previous lesson.
- We can incorporate customization into our DAX measures, by letting the user decide how many months of an offset our previous measures should have
- We need to create a single column table containing the allowed offset values and create a slicer linked to this table
- We then modify our measures to include the selected value from the slicer
Checking the value of the slicer
- We use ISFILTERED to make sure the user has made a selection from the slicer and HASONEVALUE to make sure they have only selected one value
- We can then use the value of the month's column in the offset table to represent the selected value from the slicer
- Alt JT, SF – Insert slicer
- Alt B, F – Manage measures
In the previous lesson, we created a pivot table and pivot chart allowing us to track changes in revenue over the length of our data set. However, the measures we created were not flexible and only displayed month-by-month changes in revenue.
Some users may be interested in seeing how revenue in a particular month compares to revenue two or three months before.
In this lesson, we'll create a slicer to modify the time offset in the charts created in the previous lesson.
This slicer will allow users to select the number of months for the offset. To accomplish this, we first need to add a new table to the model to accommodate the possible values.
We have 12 months of data, so we'll allow values of one to six months for the offset. I've created a column containing the numbers one to six to the side of this sheet.
We'll select the values and cut with Ctrl-X.
We'll then go to Power Pivot and paste with Alt-H-V to create the table. We'll call the table Offset. Deselect the Use first row as headers option, and select OK.
We'll then double-click the column name and rename it Months.
We do not need to create a relationship between this table and the rest of the model because this data doesn't appear in any other table.
Next, we'll return to Excel and add our slicer.
To keep things simple, we'll only slice the pivot table. Therefore, we'll select the pivot chart and delete it.
We'll now select any cell in the pivot table, insert a slicer, select the All Tables tab, add Months from the Offset table, and select OK.
We've successfully created a slicer but it doesn't currently do anything. We now need to link the measures from the previous lesson to this slicer.
Let's start by going to Manage Measures and selecting the previous revenue measure.
This calculates the sum of sales in the previous month.
We want to replace the number -1 with an expression that finds the value of the slicer.
However, we also need to deal with the possibility that the user will select multiple values or no values from the dropdown list.
We'll modify the formula and use Shift-Enter to put the number -1 on its own line.
We'll then delete -1 and add an if statement.
We need to test two conditions.
First, we'll use the function ISFILTERED on the Months column.
This ensures the user has made a selection from the slicer.
We'll then add a double-ampersand and our second condition.
Here, we'll use the function HASONEVALUE on the Months column to ensure the user has only selected one value from the slicer.
If both these conditions are met, the value of the offset will be -1 multiplied by the VALUES function of the Months column.
Otherwise, the value of the offset will be -1.
We'll then select OK to accept the modified measure.
Now if we select different values from the slicer, the Previous Revenue column changes.
For example, if we set the offset to three months, the Previous Revenue for April is the revenue for January.
Next, we need to modify the revenue change measure.
With our current offset of three, this measure works well from April onwards. However, it returns the value of revenue between January and March, which could be confusing.
Let's return to the Manage Measures window and edit the Revenue Change measure.
We'll create an if statement at the beginning of the formula and check the condition that Previous Revenue is not equal to zero.
If this condition is met, we return the same calculation as before.
If this condition is not met, we want Revenue Change to be blank, so we won't add a false condition.
We'll then select OK and see that the Revenue Change column is now only showing values when the Previous Revenue column has a value.
Finally, let's look at our percentage change measure.
We obtain this measure by dividing the Revenue Change and Previous Revenue measures.
As a result, this measure has already adjusted and we do not need to make any further changes.
As we can see, if we select different values in the slicer, all the columns in our table adjust to show only the appropriate results. If we enable multiple selection and select multiple offsets, the table defaults back to a one-month offset.
Overall, the user now has much more control over the report thanks to our use of DAX.