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:
let
Source =
Excel.Workbook(
File.Contents("C:\SixMonths.xlsx"),
null,
true
),
Source_Table =
Source{[Item="Source",Kind="Table"]}[Data],
ChangedType =
Table.TransformColumnTypes(
Source_Table,
{{"Date", type date}}
),
EndDate =
Date.From(DateTime.FixedLocalNow()),
StartDate =
Date.AddDays(Date.AddMonths(EndDate,-6),1),
FilteredRows =
Table.SelectRows(
ChangedType,
each [Date]>=StartDate and [Date]<=EndDate
)
in
FilteredRowsIn 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:
To be honest, though, I don’t think it should be this hard!
Thanks for this post, Chris. I agree – it shouldn’t be this hard – and the surprise created a few interesting “gap-toothed” bar charts where the current MTD data was omitted until I got it diagnosed.
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.
@Colin
Yep, but even the relative date slicer differentiates between months and calendar months …
Chris, do you think this is a “bug”? In my view, the “user requirements” here are not being delivered by the design, even if the developers would (likely) say “it is working as designed”.
I don’t think it’s a bug, just a case of misleading text in the UI.
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.
The blog software stripped my comments of the lt and gt signs. 🙂 My last sentence is “previous is not equal to current”.
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.
Good summation, Matt!
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:
each Date.IsInPreviousNMonths([Date], 5) or (Date.IsInCurrentMonth([Date]) and [Date]
Date.IsInPreviousNMonths(datetime, months – 1) or (Date.IsInCurrentMonth(datetime) and datetime <= Date.From(DateTime.LocalNow()))
The end of my last response got mucked up after posting. It should read:
each Date.IsInPreviousNMonths([Date], 5) or (Date.IsInCurrentMonth([Date]) and [Date]
Date.IsInPreviousNMonths(datetime, months -1) or (Date.IsInCurrentMonth(datetime) and datetime <= Date.From(DateTime.LocalNow()))
Mucked up again! What’s going on? I’ll leave out most of the end.
each Date.IsInPreviousNMonths([Date], 5) or (Date.IsInCurrentMonth([Date]) and [Date] <= Date.From(DateTime.LocalNow()))
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.
Can you give me an example of the input and expected output?
Sure
(Input string; 5-digit substring1 to pull out; 5-digit substring2 …. etc.)
P.GAL 08840/18;08840
J.DEN, 05047/18 05063/18;05047;05063
04818/18 J.SWE;04818
A.SZU 06040-18 06154-18 08657- 18;06040;06154;08657
BAU025654789;null
PD-125687598 05689/18;05689
EWA-04258;04258
5634 PD-0899; null
I am a great fan of PQ. Even on my level of understanding it does really great things.
Cheers and thank you.
Sobek
Hello,
I was brought to this post with a different problem I am having in the same ballpark. I need to set a date filter to essentially start my data from the 28th of the previous month rather than the 1st. The business context here measures a month from the 28th – 28th.
I find that all relative data filters seem to cover previous full periods. How would I go about creating filter logic which states the 28th is effective the start of the month? So that, once I get to the 29th of a month, the date filter effectively resets, much in the same was as a relative date filter would with current month.
Thanks,
Ian
Great article… had the same issue and solution too. I think it’s a little better now that the intellisense tooltip describes some of these functions, but some of the functions are a bit iffy (like In the previous N years but not including the partial month we are in…) I always feel safer explicitly excluding these rather than getting it wrong and having to diagnose it later (or heaven forbid if MS changed the function on one of these.)
I was actually poking around wondering if anyone had done any performance testing of these functions. Kind of curious how these perform when used directly in a query, vs. using these to create a
temporary variable/constant and using a Date filter, (vs. even creating a fact table and doing an inner join against it. If you haven’t, it could be an interesting test!
This post was so helpful, as I literally stumbled upon the same confusion, and found your post that helped me solve my issue. Thank you!