“In the Previous” Date Filters In Power BI/Get&Transform/Power Query

The Query Editor in Power BI/Excel Get&Transform/Power Query has a number of built-in ways to filter data in date columns relative to the current date, such as the “In the Previous” option. However these filters behave in a way I find non-intuitive (and I’m not alone) and it’s not obvious how to get the behaviour I think most people would expect. In this post I’ll show you what the built-in relative date filters actually do and how you can get change them to do something more useful.

Let me give you a simple example. Imagine you’re using the following table of dates (in DD/MM/YYYY format) in an Excel table as a data source:

Now, let’s also assume that the today’s date is January 8th 2018 and you only want to load data from the last six months. If you load the data into Power BI in a new query:

…and then click on the dropdown menu in the top right-hand corner of the Date column (highlighted), you can select Date Filters/In the Previous:

…and then set up the filter for “Keep rows where ‘Date’ is in the previous 6 months” as shown here:

…you get the following table back:

Six out of the seven dates in the original table are returned, but not the six I would expect. Remember that today’s date is January 8th 2018, and notice that January 1st 2018 is not present in the filtered table and July 1st 2017 is present! I don’t know about you, but I would say that January 1st 2018 should be considered as being “in the previous 6 months” and July 1st 2017 should not be.

The reason this is happening is that the M code generated by the UI uses the Date.IsInPreviousNMonths function, so as a result the filter is getting all the dates that are present in the six months before the month that today’s date is in. Hmmmmm.

In many cases you can get a “last six months” filter of the type I would expect quite easily, by altering the filter dialog box shown above to filter by the last 5 months and including an Or condition that also filters by the current month, like so:

This returns the following table:

You’ll see now that January 1st 2018 is present and July 1st 2017 is not present. However you will need to be careful with this: if your source data contains dates that are after today’s date but in still in the current month, these dates will now also be included! For example, if the source data is changed to include a new row for January 31st 2018:

This new filter will include January 31st 2018 because it is in the same month as today’s date:

What if you want to exclude dates that are after today but in the current month? This is where things get tricky, and where you’ll need to write some M code. Let’s imagine that you want to get all the dates that occur in the range July 9th 2017 (the day after the date that is six months before today) and January 8th 2017 (today). You can do this by editing the original query as follows:

In this query, the EndDate step returns today’s date using DateTime.FixedLocalNow(), the StartDate step returns the day after the date that is six months before today’s date, and the FilteredRows step filters the dates so that only those that occur between StartDate and EndDate are returned. And yes, I checked, if you do this with a SQL Server data source then query folding does occur.

With this query, you finally get the dates you’d expect from your filter:

Hi Chris, I actually prefer the current implementation. I consider January to be the current month, and not a previous month. This is important for reports and charts based on monthly data. Since the current month has not yet ended, including the data for it would be misleading. However, if I wanted to include the current month, I would simply select the appropriate choices in the UI – which would generate the following (partial) code:
…each Date.IsInPreviousNMonths([Date], 5) or Date.IsInCurrentMonth([Date])

I guess it’s a matter of taste. I can see the logic of the current behaviour, but I think it could be explained better in the UI and I also think there are a lot of equally (if not more) useful date filters that are not easy to implement, like the one at the end of my post.

Yes, maybe my use of the word “bug” is incorrect. And I think also we are saying the same thing. If a user wanted to select “in the last 6 months”, I doubt anyone would want to exclude the current month from that. So in my view the developer built this as s/he interpreted the user requirement (or as the function supported a quick solution out of the box) but the solution doesn’t meet the user requirement or expectation. So while it is probably ‘working as designed’, the design doesn’t meet the user requirements. JMO. Thanks for pointing this out.

I believe there is a semantic difference between “in the last 6 months” (which Chris and Matt have been using in the comments) and “in the previous 6 months”. The PBI editor clearly says previous. Current previous.

I disagree: I don’t think there is an unambiguous semantic difference. I was caught out by this, I’ve had a customer caught out by this, and I’m sure many other people have been too. It’s easy to see the difference between “last” and “previous” when you know what the M is doing but most Power BI users won’t see the difference immediately. The UI certainly does not make it clear what’s happening.

I think that the point that is being missed is that M date logic distinguishes between “previous”, “current” and “next”. January *cannot* be both the current and the previous month at the same time. That’s why the logic must include both previous and current, if you want to count the current month in the last x months.

“If a user wanted to select “in the last 6 months”, I doubt anyone would want to exclude the current month from that”
It’s quite possible that I might be the only exception :). However, if that’s what you want, it’s a simple “or” operation in the UI, as described in my previous post. In addition, it should not be forgotten that the date logic functions provide a lot of flexibility in filtering dates in general.

We can agree to disagree. But my point is that we’re filtering dates here and not months, and I’m sure if you asked most people (including most Power BI users) whether January 1st was “in the previous six months” as of today they would say yes. I also discussed exactly the “or” operation you mention in my post and point out that it also includes dates after today, which is going to be a problem for some people too. Anyway, as I’ve said in other comments on this post, I think the real issue isn’t the functionality but the descriptions in the UI, which could be clearer.

Hi Chris, I must say that in the beginning I spent some short time to understand how relative filters work for dates in Power Query Editor. It wan’t obvious for me if current month is included or not, but wasn’t difficult to test and find out. After, I got used to this behaviour and now I find it right. In my practice, I mostly work with whole month periods + “This Month”, so I’m happy with current M functions, they allow me to cover all my scenarios. I only upset that there is no default Date.Today function.
However, agree that UI can be extended with another branch of dropdown menus for “*(Calendar)” month / year filters like it is done for Date slicer in Power BI.

“But my point is that we’re filtering dates here and not months, and I’m sure if you asked most people (including most Power BI users) whether January 1st was “in the previous six months” as of today they would say yes”

As you point out in your original post, M is using the function Date.IsInPreviousNMonths, which is same thing expressed in the UI. Once you use this option in the UI, you are filtering by months, not dates (at least not as far as PQ is concerned). Filtering by dates requires that use actual date values.

“I also discussed exactly the “or” operation you mention in my post and point out that it also includes dates after today, which is going to be a problem for some people too”

I sincerely apologize. I completely overlooked this point in your original post.

“I think the real issue isn’t the functionality but the descriptions in the UI, which could be clearer”

The problem is that if “previous” in the UI included the current month up to the current day, it would be inconsistent with how the corresponding M function works, and that would be *very* confusing. I think that what you need in the UI is something like “Previous Inclusive,” but then PQ would have to generate M code like:

Hi
a little off topic, but it is nagging me. I need to pull a substring consisting of 5 digit (only 5 digit). There may be several such substrings in a original string. This is all to be done with PQ. Any thoughts anyone?
Regards
S.

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk