Best way to dynamically filter date table in Power Query

[vanessafvg]

what is the most efficient way to limit the dates in the date table in power query.

I am pulling all my tables from the datawarehouse via sql.  I want to limit the dim date dynamically by only including data for the last 4 years, so that 3 years historical and 1 year ahead because i have a lot of rows in that table.

What is the best way to do this?

tia!

[v-jiascu-msft]

Hi @vanessafvg,

Could you please mark the proper answer or share your answers if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

@vanessafvg

Hi,

There are two ways to do this.

1. Create a view in the Data Warehouse. Then we can connect to the view from Power BI. Maybe it looks like this: 

create view FourYears 
        as select * 
        from daxbook.Date 
        where [Calendar Year Number]<=year(getdate())-4;

2. Edit the query of Power BI in Advanced Editor. Maybe it looks like this:

let
    Source = Sql.Database("DB", "contosoretaildw", [Query="select * from daxbook.Date where [Calendar Year Number]<=year(getdate())-4;"])
in
Source

Please have a try.

Best Regards!

Dale