One of the nice features of Power BI is the ability to select slicer configurations in design mode which are then exposed to the user once they open the report. So if it’s July of 2016, I can select 2016 on a year slicer, and all the months January through July to show a user year to date data on a line chart, like this:
The issue comes in when next month rolls around, and you have to reopen the report, control-click on August to add it to the default slicer selection and republish. Not a lot of work if you have one report, but if you have a bunch of reports with a bunch of time slicers this can get tiresome quickly.
Wouldn’t it be nice to have slicers that say things like “Current Month”, “Next Months”, and “2 Months Back”, so that if you want to always show a window of time relative to the current month, you set the slicers once and they’ll always work? And even better have a way to add page level filters for months that don’t need to be updated every month? Something kinda like this:
To do this all we need is a Relative Month Index column and Relative Month Index Label column. Big names yes, but easy concepts I promise. The first just says, for each date in the dates table how many months forward or backward is that from the current month. So in the dates table, assuming today is July 26th 2016, the row for June 3rd, 2016 has a relative month Index of -1, because that date (happening in June) is in a month 1 before the current month (July). The label is the same thing but rather than being comprised of numbers (which work great for page level filters) it’s comprised of text (which work great for slicers, like in the example above.) Here’s a snapshot of what they look like:
So how can you make these happen in your report? I’ve got a couple videos to show you how. The first shows you how to use a free tool we at CSG built to quickly add these to any existing date table you’ve got with some explanation as to what it’s doing. The second is the quicker, dirtier version which shows you how to use the tool much faster, but with very little explanation (aka I just found this tool and I need to get it working in the next 10 minutes or less.) The third version is how to add the columns on your own from scratch which could be useful if you want to create some variations to suit your specific report.
Using the CSG Pro Relative Date Generator to quickly add relative dates to you existing calendar:
“Up and Running” version of the same thing:
Starting and Completed PBIX files.
The CSG Pro Relative Date Generator Tool
CSG Pro Relative Date Generator
(It’s just an M Script stored in a text file, create a new blank query and in the advanced editor paste this script over the existing script)
