Dynamic filters on Tables

[kdixon5490]

Currently I have a table called data_sale that has all my sales done and this table can get very large.   Here is what the Advance Editor under query has...
let
Source = Sql.Database("ORL-WKS-KDIXI01", "BOS_TRR_Prod_COA"),
dbo_DATA_Sale = Source{[Schema="dbo",Item="DATA_Sale"]}[Data]
in
dbo_DATA_Sale

I need to put a filter on the column fsaledate to be the last 3 months only  fsaledate >= (dateadd(mm, -3, getdate()).  

How do I do this?   I tried several things and I keep getting this...

Expression.Error: We cannot apply operator < to types Table and DateTime.
Details:
Operator=&lt;
Left=Table
Right=12/1/2016 12:00:00 AM

My advance editor looks like this when I get the error.   (BTW  I created another query that get the StartDate and I called it StartDate) 

let
Source = Sql.Database("ORL-WKS-KDIXI01", "BOS_TRR_Prod_COA"),
dbo_DATA_Sale = Source{[Schema="dbo",Item="DATA_Sale"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_DATA_Sale, each [FSaleDate] > StartDate)
in
#"Filtered Rows"

Any Help would be appreciated.

[Stachu · Accepted solution]

if the column type is set to Date you should see date filters

formula generated is somthing like this:

= Table.SelectRows(#"Renamed Columns", each Date.IsInPreviousNMonths([MthDate], 3))

you would obviously adjust the names to your setup

[ChrisMendoza · Accepted solution]

hello @kdixon5490,

Try Date.IsInPreviousNMonths( ). 

[FlaF]

Hi,

In edit query, you can filter your data. You have to click on the column fsaledate and select only the last 3 months

Regards

[kdixon5490]

How do I make it dynamic to always no matter what day I look at the report to always grab the last 3 months?   Today's last 3 months is different than next weeks last 3 months.   

I hope that makes sense.

[FlaF]

Yes I understand and you're right. I haven't explain it very well. You can enter in edit query mode and click on your column, then you can find the voice "date filters" in the drop-down menu of the column: "date filters" --> "Quarter" --> "Last quarter"

[Stachu · Accepted solution]

if the column type is set to Date you should see date filters

formula generated is somthing like this:

= Table.SelectRows(#"Renamed Columns", each Date.IsInPreviousNMonths([MthDate], 3))

you would obviously adjust the names to your setup

[Stachu]

This should work:

= Table.SelectRows(dbo_DATA_Sale, each Date.IsInPreviousNMonths([[FSaleDate]], 3))

although the error indicates that you try to filter a table rather than a table column - when you filter [FSaleDate] manually for e.g. last month, how does the syntax look like?