Filtering a date column

[Samuel_sousa]

Hello,

Let me put you on context first:

1. There are two tables in direct query connection mode

2. I have one fact Table which i had to split in two, the criteria to split both is the date in order to obtain one table with the last four month data, and the other table i want the exact opposite, this means to store all dates which are not in the last 4 months & the current month. The first table wasn`t a problem at filtering i just filtered to keep the last 4 months & the current month dates, but the other one i cannot find how to. I tried this on the steps panel: 

= Table.SelectRows(#"Columnas quitadas", each [DateColumn] <> Date.IsInPreviousNMonths([DateColumn],4)). But with this filtering the table just keep loading, and never ends.

Please it would be very helpful some hand here, Thanks!!

[BA_Pete]

Hi @Samuel_sousa ,

Two things:

1) You try a filter condition something like this:

[DateColumn] <= Date.AddMonths([DateColumn], -4)

 2) Why have you split the table in the first place? In a Direct Query model, nothing is loaded to the model, so it's not a space- or time-saver. You can make these timebound distinctions in the data with measures for much greater performance.

Pete

[Samuel_sousa]

Hello Pete,

I split that table because its too large to be treated, so i need a table with "hot" data which is data with the last 4 months, and "cold" data, which is the data prior to those last 4 months.

I`ve tried the formula you`ve writen, unfortunaly it dindt work:

Samuel.

[BA_Pete]

I'm not sure what you mean by "too large to be treated". You're using Direct Query so no actual data is being loaded to the model. Any transformations you make will be folded back to the source, so there should be no issues with size whatsoever. For the record, I don't think you should be trying to split your table like this at all when using a Direct Qury model. However:

...regarding the failed filter: when you say it's not working, what do you mean? Is it not giving you the required output, or are you getting an error message?

Pete