How to take the max date from one table and pass it as a filter in another table while extracting.

[ZikoPowerBI]

Hi All ,

I have the following use case :

1. There are two tables one fact table and other is calendar table / Date table.

2. Both the tables come from Database using Direct query.

3. I Have to take the max date from fact table and and pass that date to Calendar table by filtering the date column while fetching the data. (I should be able to filter the data in Calendar table for only dates less than or equeal to the max date of the fact table).

I have created a reference table of fact table and in transform selected the latest date.

I have no clue was has to be done ahead.

Can anyone please help me to achieve this.

I referred the following link :

https://www.ehansalytics.com/blog/2019/3/17/create-a-dynamic-date-table-in-power-query

@powerquery

[ZikoPowerBI · Accepted solution]

Hi All , 

Got through the appropriate answer to it :

While fetchoing the table using direct query we transformed and set the filter on the date 
sample filter code : 
table.selectedrows(Calendar,each[date] <List.Max (date) ))

[Uddin747]

Hi there, it's been a while, but can you please tell where do you enter this code? and here which is the fact table & the date table? table.selectedrows(Calendar,each[date] <List.Max (date) ))

I have the same problem - have to limit the date table according to the max date in fact table in Power query

Thanks in advance!

[lbendlin]

3. I Have to take the max date from fact table and and pass that date to Calendar table by filtering the date column while fetching the data. (I should be able to filter the data in Calendar table for only dates less than or equeal to the max date of the fact table).

You _can_ do that by allowing the search filter to flow from the fact to the dimension. But that is highly discouraged in a star schema.  It will probably be easier to generate a measure and use that for the extract.