cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
plerner Frequent Visitor
Frequent Visitor

Filter query by date column

I am trying to reduce the amount of rows that Power BI querys from the database, so in the query editor I am adding a filter on a timestamp column from the data source. In the filter options i have choosen "is in the previous", and then selected 6 "months". It appears to calculate first which where the 6 previous months, leaving the current one out, and then it brings the data that belongs to them, but this leaves out all rows from the current month. How do I create a filter that will bring me the data from 6 month ago, including my current month, up to today?

2 ACCEPTED SOLUTIONS

Accepted Solutions
cwebb
Advisor

Re: Filter query by date column

This drives me nuts! I agree, I expect "is in the previous" to include the current month too.

 

However you can do what you want. First of all, on the dropdown menu at the top of the column, choose Date Filters/Month/This Month. This will give you a filter that filters out all the data from the current month. Next, go to the Applied Steps pane and click on the gear-shaped icon next to the newly-created Filtered Rows step. This opens up a dialog that allows you to edit the step. You'll see a condition that says "is in month" equals "this month"; underneath it select the Or radio button and then add a new condition that is "is in previous 5 months". It should look like this:

 

IsInPrev.PNG

 

Click OK and you should be good.

 

HTH,

 

Chris

cwebb
Advisor

Re: Filter query by date column

It's worth pointing out that this filter will return any dates that are in the current month but after today's date - which may not be what you want. I've written up this problem and a full solution in a blog post: https://blog.crossjoin.co.uk/2018/01/08/in-the-previous-date-filters-in-power-bi-gettransform-power-...

 

Chris

8 REPLIES 8
plerner Frequent Visitor
Frequent Visitor

How to filter a date time column?

I am trying to reduce the amount of rows that Power BI querys from the database, so in the query editor I am adding a filter on a timestamp column from the data source. In the filter options i have choosen "is in the previous", and then selected 6 "months". It appears to calculate first which where the 6 previous months, leaving the current one out, and then it brings the data that belongs to them, but this leaves out all rows from the current month. How do I create a filter that will bring me the data from 6 month ago, including my current month, up to today?

Matjo Regular Visitor
Regular Visitor

Re: How to filter a date time column?

This works for me: 

Solution.jpg

Best regards,
Mattias

cwebb
Advisor

Re: Filter query by date column

This drives me nuts! I agree, I expect "is in the previous" to include the current month too.

 

However you can do what you want. First of all, on the dropdown menu at the top of the column, choose Date Filters/Month/This Month. This will give you a filter that filters out all the data from the current month. Next, go to the Applied Steps pane and click on the gear-shaped icon next to the newly-created Filtered Rows step. This opens up a dialog that allows you to edit the step. You'll see a condition that says "is in month" equals "this month"; underneath it select the Or radio button and then add a new condition that is "is in previous 5 months". It should look like this:

 

IsInPrev.PNG

 

Click OK and you should be good.

 

HTH,

 

Chris

cwebb
Advisor

Re: Filter query by date column

It's worth pointing out that this filter will return any dates that are in the current month but after today's date - which may not be what you want. I've written up this problem and a full solution in a blog post: https://blog.crossjoin.co.uk/2018/01/08/in-the-previous-date-filters-in-power-bi-gettransform-power-...

 

Chris

plerner Frequent Visitor
Frequent Visitor

Re: Filter query by date column

That is a nice detail to think about.

ppei Frequent Visitor
Frequent Visitor

Re: Filter query by date column

Chris,

 

I need to add a filter to my timestamp column to show only the previous 7 days (or one week) data. I read your blog and tried your solution. However, it didn't work for me. Mine didn't do any filter at all. My TimeOfSample column is data/time data type. It shows like this - 7/27/2018 12:00:55 AM. Is that special format that is the cause of not working? Do you know how I can resolve this problem?

 

Thanks in advance.

 

Pei

ppei Frequent Visitor
Frequent Visitor

Re: Filter query by date column

Chris,

 

I need to filter a timestamp column to show only last 7 day's data. I used "in the previous" 7 day filter, but it didn't do any filter. The timestamp column data type is date/time and it is in this format: 7/27/2018 12:00:55 AM. I don't know why it didn't filter anything. Do you think it may be related to the special data format? What do you suggest to resolve this problem?

 

Thanks in advance.

 

Pei

cwebb
Advisor

Re: Filter query by date column

Hi Pei,

 

Strange  - so the filter did not remove any rows at all? Can you try creating a new column in the Query Editor with just the date values in, and applying the filter on that column?

 

Chris