Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Proud to be a Super User!
Solved! Go to Solution.
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
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
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
User | Count |
---|---|
98 | |
91 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |