Forum Discussion
Dynamic filters on Tables
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=<
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.
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
6 Replies
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?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
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.
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
