When working with Power BI filters, there is a need for you to set them to roll over automagically especially when working with date filters. One of the common requirement is to have a month/year filter that rolls itself over when time comes. At the face of it, it seems like an uphill task but it actually isn’t. Here is a simple walk through on how to change those months when times comes.
Create a custom Column – Power BI has different ways to create custom columns one of them exposes the column to be used for conditioning and the other one doesn’t. We need the one that allows conditioning.
- Open your Power BI Desktop and click on Edit Queries under External Data grouping.
Edit queries gives you access to the modeling platform of Power BI and there is quite a bit that can be accomplished here but we’ll stay on task
- Click on Add Column as show below
We will create a custom column that will inherit the current month number then use that month number to set our actual month in our filters.
- Click on Custom Column from the General Grouping and a formula window will be displayed, you’ll probably have available columns, I have concealed mine for the time being.
- Now we need to define the M Query formula that will give us back the current month number that we can do comparison with our date dimension (date table). In the formula window add the following text. For more information about the M Query you can visit
- https://msdn.microsoft.com/en-us/library/mt296609.aspx
Number.ToText(Date.Month(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)))
- Once done Click OK, you should now have a new column with the current month number – this month number will dynamically change every month.
- Change the column data type to Whole number from Text
- Now that we have something to Condition with, we can create our conditioning formula. On the ribbon, click on Conditional Column which will allow us to set that new column that you can use as a report filter.
- Conditional Column allows us to pass in a string or full data from existing report. In this case, data is coming from date dimension thus I have the following columns at my disposal.
- Calendar Month Number
- Month Name (my targeted column to change)
- The whole output is as follows
- Once done click OK and now you should have a column that you can use for your month(s) filter that will change month over month.