Think back to the year 2012 when Microsoft introduced us to SQL Server Analysis Services Tabular models. It was new and exciting. The Excel gurus who had been using PowerPivot since 2010 were sneering at us like we were crazy. We could now build a model, publish it to SSAS and have a secure, centralized and highly optimized server solution for the Enterprise that didn’t require us to learn that nasty language called MDX (multidimensional expressions). Although DAX was still new, it made some sense to most Excel users and was much more usable by us “normal” people.
Before Power BI Desktop was available, we would point our Excel reports to this new and exciting SSAS Tabular option, and we had a high-performance ad hoc query capability. We built dashboards in Excel and published them to SharePoint, and we were in a happy place.
Then a realization set in! Every month we had to check the report out of SharePoint, update the date filters to the current month and check it back in. For those rolling 12-month reports, we had to check it out, add the new month and remove the old month, so on and so forth. Soon this got old, and we weren’t quite as happy as we once were.
Convenience Filters Defined
In 2013, I set out to fix this annoying issue and came up with something I call “convenience filters.” Let me define for you what I mean by convenience filters. Most of our tabular models should have a date table. In this date table, we typically have a date column and then several other ways to look at that date – Year, Quarter Name and Month Name are just a few examples. My solution was to add a few more columns to this date table that would hold the values “TRUE” or “FALSE” so that in our Excel report we could add one of these columns in the filter area and set it to “TRUE” and never worry about it again.
For example, a new column called Current Month. For each row in our date table, the date would be evaluated against the current date, and if it was in the current month, then this value was set to TRUE. If it wasn’t in the current month, it would be set to FALSE. Now, instead of going thru the painful process mentioned earlier, just add Current Month filter to our report and set it to TRUE. Walla! Problem solved, and we are happy again.
Power BI Desktop Offers Some Help
A word of praise to the Power BI team. They realized this as a problem and recently added something called relative date filtering. With relative date filtering, we can add a date to the filter area of a page and choose relative date filtering.
With this, we can do some beautiful things such as Show items when the value “is in this” and pick the day, week, month or year. We can also do things like Show items when the value “is in the last” 12 months so that we have a rolling 12 months. This would solve the problem if you were using Power BI Desktop for reporting. But what if you are still using Excel to hit your SSAS Tabular models or, God forbid, some other tools out there like Tableau or Cognos.
Building “convenience filters” into your SSAS Tabular models is a way of centralizing this technique so that they can be used by any reporting tool that can access SSAS Tabular models (and many can).
Let’s Build Some Convenience Filters
The technique will vary depending on where and how you built your date table. In some cases, your date table may be physical tables in your database or at least views in your database. In other cases, they may be generated with DAX in your model. In either case, the technique is the same; just the implementation is slightly different.
Date table in SQL Server
For those of you who want to materialize these columns in a physical date table or view in your SQL Server database, you can add T-SQL code like the examples below. In these examples, PK_Date is the date column in our date table.
Current Month – CASE WHEN YEAR([PK_Date]) = Year(getdate()) And Month(PK_Date) = Month(getdate()) then ‘TRUE’ else ‘False’ end as Current_Month
Current Year – CASE WHEN YEAR([Date]) = Year(getdate()) THEN “TRUE” ELSE “FALSE” END as Curent_Year
Rolling Three Months – CASE WHEN DATEDIFF(month, PK_Date,getdate()) BETWEEN 0 AND 2 THEN ‘TRUE’ ELSE ‘FALSE’ END AS RollingThreeMonths,
Rolling Twelve Months – CASE WHEN DATEDIFF(month, PK_Date,getdate()) BETWEEN 0 AND 11 THEN ‘TRUE’ ELSE ‘FALSE’ END AS RollingTwelveMonths
User DAX to create convenience filters
For those of you who wish to use DAX and create calculated columns in your model then the following examples should get you started.
YEAR ( Dates[PK_Date] ) = YEAR ( NOW () )
&& MONTH ( Dates[PK_Date] ) = MONTH ( TODAY () ),
IF ( YEAR ( Dates[PK_Date] ) = YEAR ( NOW () ), “TRUE “, ” FALSE” )
Rolling 12 Months =
Dates[PK_Date] > EDATE ( TODAY (), -12 )
&& ( Dates[PK_Date] <= EDATE ( TODAY (), 0 ) ),
Organization is key
Luckily, I don’t organize models the way I organize my office. With SSAS Tabular, you can use the Display Folder property of a column to group your convenience filters into a folder so your date table will be organized and clear to your user’s.
There are many other very useful convenience filters that you can implement. Get creative based on your business needs. Below are just a few ideas to get your juices flowing…
Last Loaded Month
Last Month of Previous Year
Need a Jump Start! We Can Help.
If you need help building enterprise-wide SSAS Tabular models, let us know, and we can help.