Author: Kane Snyder – Principal Consultant at Agile Analytics
I told myself that I would stop blogging about calculation groups this year and delve into other discoveries I’ve made in my Power BI adventures but its just so damn hard! More and more they keep coming up in solutions to complex requirements and effective report design. In this post I want to look at a technique we implemented in a project to mix static column values for Quarter Years with relative date periods based on TODAY(). The technique allows you to mix static column values with dynamic DAX based filters in the same slicer. Shout out to my team mate, the amazing Owen Auger, who worked with me on the project!
Project Requirements:
Our clients requirement was to create a date slicer combining Quarter Year values present in the fact table and relative month periods (“In the last 3 months”, “In the last 6 months”, “In the last 12 months”…..”Q4 2021”, “Q3 2021”, “Q2 2021”, “Q1 2021″……) all within a single slicer.
Our initial solution was to create a period table with our slicer values in one column, the dates calculated for each slicer value in a second column and a sort column. We had a current date value in Power Query that we could base our relative periods on so whenever the dataset refreshed the period table would update with Quarter Year present in the fact table and relative periods based on the Power Query refresh date. The period table was used to filter our date dimension.
This was all great until the client dictated that all relative date logic for the project should be based on the TODAY() DAX function. The concern was that if the daily refresh failed, the relative periods would be incorrect. It was important to them to see the status of the data in relation to the current day no matter what. So we couldnt use the relative periods from the period table we created as they only updated on refresh. To apply a filter that calculates dynamically on page load, we would need a calculation group. Unfortunately calculation items cannot be dynamically created so there wasnt a way to add Quarter Years as calculation items and have them dynamically update depending on what was present in the fact table. How could we combine our Quarter Year values present in the fact table with relative date periods based on TODAY() in a single slicer?
The Solution:
The concept is actually relatively simple. We kept the period table and created a calculation group for the relative date periods that only kicked in when the relative periods were selected otherwise it did nothing. This was achieved by simply creating a calculation item with some conditional logic based on the SELECTEDVALUE() of the period table. So when a user selected a relative date period from the period table the calculation item was enabled filtering the report based on TODAY() otherwise selecting a Quarter Year filtered the date table through the relationship with the period table using the model. Mixing standard filters from column values with filters from calculation items got me excited. If you read my previous post on calculation group performance ( A Technique for Optimising Calculation Group Performance (agile-analytics.com.au) you might notice some similarity. Calculation groups in combination with calculated tables and SELECTEDVALUE() is very useful for creative solutions. This was a fairly simple example but this idea of turning on calculation items with certain user selections in slicers could allow you to be very creative with report design or might help you meet a complex requirement. An example I can think of would be mixing dynamic segmentation of products with actual products. In the real world there would be plenty of unique business requirements where this could be useful.
Specifics:
In our project we didnt have any date axis or columns on the page so the implementation of this technique was a little simpler but here I show how you can make it work with a time series. Shoutout to SQLBI and this fantastic article which taught me some of the concepts required to make relative date periods in a calculation group work with a time series chart:
Show previous 6 months of data from a single slicer selection – https://sql.bi/704248
As I mentioned the concept is relatively simple – use conditional logic with SELECTEDVALUE() to make calculation items enable on specific user selections. The complexity in this example comes from using dates and the introduction of a time series chart. Im using SQLBI’s Contoso model from Definitive Guide to DAX Companion Content.
Period Table:
Im sure we probably created this in Power Query in our project but here I’ve used a DAX calculated table.
Essentially, you just need to take the column values you want in your slicer (in this case Quarter Years), the associated dates and a sort column (Quarter Year Number) and then append this to some text to reference in the slicer for your dynamic date ranges baesd on TODAY(). For the dynamic date ranges, Ive left the dates blank and added a sort value to keep them at the top of the slicer. I aslo filtered the Quarter Years using a DateWithSales column in the Date table to ensure only dates in the fact table are included. You can do the same in Power Query following similar logic.
The Period table is connected to the date table with ‘Period'[Date] to ‘Date'[Date] and I have set the relationship to bidirectional to allow ‘Period’ to filter ‘Date’:
So, when a Quarter Year is selected in ‘Period’ it filters ‘Sales’ through ‘Date’ and when a relative date period is selected from ‘Period’ it filters nothing because the date values in ‘Period’ are blank for those items.
I then create a calculation group called ‘Period Override’ and a single calculation item (Relative Date Filters) which will be used to override the filter coming from the ‘Date’ table when a user selectes a relative date range.
Here is the code for the calculation item. Note that the dataset Im using for the example has no current day data in it so in the TodaysDate variable, I have just referenced January 1st 2009 but this would be TODAY() instead.
Some explanation of the DAX:
After the TodaysDate variable which sets the starting date for our relative periods, there are three “MinusMonths” variables which create tables of dates to use for our relative periods (last 3, 6 or 12 months from VAR TodaysDate value).
The VAR Selection variable is used to store the users slicer selection by referencing the ‘Period'[Sort] column with 1, 2 and 3 being our relative periods. Heres where it gets a bit tricky. We need to use MAXX to pull out the value rather than SELECTEDVALUE () so that we can use calculate to disable the relationship between ‘Date’ and ‘Period’. The relationship needs to be disabled so that when our calculation item overrides the filtering, we dont make our period slicer selection invalid.
In VAR Result, we use SWITCH to apply the different date range filters needed for the users selection in the slicer. For example, if “In The Last 3 Months” is selected this will have a sort value of 1 and will return the first CALCULATE statement in SWITCH which filters the last 3 months. We use KEEPFILTERS on the VAR Minus3Months date table to not override the original filter context in the visual. Without KEEPFILTERS, we would get all dates and the same value in the visual. We use CROSSFILTER to disable the bidirectional relationship and stop ‘Period’ from filtering ‘Date’. This is so that our selection in the slicer (‘Period’ table) does not filter ‘Date’ and interfer with the new filter we are applying. The last “Else” part of the SWITCH statement is SELECTEDMEASURE() so that when 1,2 or 3 (Our dynamic date ranges) are not selected, our calculation item is deactivated and the period table filters as normal using column values.
In a Report:
To use our solution in a report we simply need to add our ‘Period'[Period] column to a slicer and then add our ‘Period Override'[Relative Date Filters] calculation item as a page level filter.
Wrap Up:
The result is a slicer that uses column values updated at refresh combined with filters from a calculation item that calculate on page load. The ability to mix these two types of filters together opens up interesting possibilities and gives a lot of flexibility. Im curious if you have come across this requirement before, let me know what you think.
Note: I originally posted this with some code that applied the calculation item to a specific measure only (Sales Amount) but that restricts the power of using calculation groups and the advantage of this solution is that it can work across all measures on a page withought having to reauthor any of your measures. If you only needed to apply this to a single measure, you could probably push the logic into that measure. Thanks to Nishant Chawla for pointing this out on LinkedIn.
This blog was compiled by Kane Snyder – Principal Consultant at Agile Analytics. You can also follow Kane on LinkedIn here.
