Filter a query based on a another table

[AnaBraganca]

Hello,

I'm struggling to filter a database based on a date listed in another database.

I've seen similar problems here, but so far nothing worked, or I'm getting the coding wrong.

I have a database called Existencia, with several rows and three columns. One of the columns has dates, column Dat1. 

I have another database, called Analise,  and I want to filter the date column - called Dia - in the database Analise, to be on or before the latest date from Dat1 in the Existencia database. So I want ----->   Dia <= latest Dat1 

Note: The latest date is constantly changing, the Existencia database source is an Excel file, so new rows created constantly. 

I don't want to merge or append, and want to avoid creating another column in the Analise query to meet the condition.

But I'm struggling with the language to filter in the Advanced Editor.

[v-yilong-msft]

Hi @AnaBraganca ,

I create two tables as you mentioned, their names are Existencia and Analise.

Then I create a new table and it satisfies your requirements.

Table =
VAR LatestDate =
    MAX ( 'Existencia'[Date1] )
RETURN
    FILTER ( 'Analise', 'Analise'[Dia] <= LatestDate )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnaBraganca]

Hi,

I'm trying to avoid creating another table.

What I'm trying is for this filter to be each [Data] <= the latest date of the Dat1 column (second picture). But this second part of the code is the one I'm struggling with.

This is the table with the date I want to filter with.

In this case, I want the code to filter by 30-06-2023, which will change when a new row is added. 

[v-yilong-msft · Accepted solution]

Hi @AnaBraganca ,

I think you can use Parameter in Power Query.

Then you can choose Date Filters and select Before.

Finally you can get the whole days before what you need.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnaBraganca]

I've tried this before going for the Advanced Editor option, but I could never get the parameter to automatically get the last date of the Existencia table.