In this blog, we will be seeing how we can show the previous 6 months of data from single slicer selection for multiple calculations using calculation groups in Power BI.
Calculation Groups –
Calculation groups are a collection of items, which are basically the same measures that we create in our report but are created in a slightly different way. Calculation groups are created in Tabular Editor, which we can download for free from the internet. We can see the Tabular Editor in your External Tools.
For Example –
If we have Sales, Quantity, Revenue, and Cost, and we normally need to add 4 – 5 measures to calculate MTD for each. By using Calculation groups, we will only have to create 1 measure and apply it to all.
Why Should we do this?
To reduces the number of measures that one must create.
How can we do this?
Below are the steps that one needs to follow to create the calculation group. Here, we need to show the sales amount, and the margin percentage calculations using a calculation group that changes the behavior of any measure. Also, it is flexible as we can use the same logic for any other measure.
Data Modelling –
Regarding the data model, we have created two relationships between the ‘Date’ table and the ‘Sales’ table. The active relationship is using the order data and the inactive relationship is using delivery data as shown below –
Challenge –
What we can use to show the different months with multiple calculations using calculation groups. As our Year and Month slicer is from the Date table and is filtering only selected months as shown below –
Solution –
Step 1
We’ll be creating a new table named ‘Previous Date’ that will contain the same values as the data.
Measure
Previous Date = ‘Date’
Step 2
Now, after creating the ‘Previous Date’ table, we need to create the relationship of the table with the remaining part of the model. Here, we can create an active relationship between ‘Previous Date’ and ‘Sales’. But it’s better to create an inactive relationship between ‘Date’ and ‘Previous Date’ which will be having a many-to-one relationship. This is because we need ‘Previous Date’ to Filter ‘Date’ and not vice-versa. And also, we need full control over the filter that is working on the ‘Previous Date’.
Below is the data model for the same –
Step 3
Now, we must build the logic and create a measure to go 6 months back from the currently selected date in the date table.
Before building the logic, we need to pay attention to some details –
- The new filter (with 6 months) must replace the active filter on the Date. Therefore, we should remove any filter on the Date.
- The new filter always contains 6 months. We need to intersect the new filter with the filter coming from the visual, which contains a single month.
The below calculation item works together with the ‘Previous Date’ table to show 6 months back from the currently selected date in the ‘Date’ table.
Measure –
Step. 4
Now we will use the measure which we have created for sales amount for the previous 6 months. Then, we’ll create the calculation group that applies the same logic instead of computing the sale amount we’ll be computing the Sale Amount and the Margin % both.
Below are the steps to follow to get the result by creating the Calculation Group
Step 4.1
Go to External Tools > Tabular Editor
Tabular Editor will open. Then select the measure which you created to calculate the previous 6 months’ sales.
Step 4.2
Copy the measure and create a new calculation group named Previous Months to Show as shown in the below image –
Step 4.3
After creating the calculation group named ‘Previous Months to Show’, we will give the same name i.e., ‘Previous Months to Show’ to the Name column and, we’ll create a new calculation item named ‘previous 6 Months’ in that group as shown below –
Step 4.4
We’ll make a new measure as given below. This calculation item works together with the Previous Date table to show 6 months back from the currently selected date in the Date table.
Measure:
Step 4.5
Now, we’ll go first save the changes in the Tabular Editor and then Power BI Desktop report and refresh the report.
Step 4.6
We’ll take Prev Month to Show and use it in the filter pane for this visual and I’ll apply a filter of Prev 6 Months which we have created as shown below –
Result:
Now we can see the previous 6 months’ data from single slicer selection for multiple calculations i.e., Sales Amount & Margin % using Calculation Groups as shown below –
