Forum Discussion
Filter the data in tables
Hi Experts,
When I try to build the dashboard and as the first step I picked Direct Query.
Later selected all the tables that I need to start build the dashboard.
I then go to transform data ? then rename the tables/fields.
During this stage I only want rolling 3 years worth of data. How can I do it?
Appreciate your help.
Hi Anonymous
Go to Transform Data, click on the column header and change the filtering options. For more information, please read this article, https://radacad.com/only-get-the-last-few-periods-of-data-into-power-bi-using-power-query-filtering
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous , do You want that in visual or you have to change it import mode and you want three years of data ?
Three-year data based on ?
same based on today
3 year3 Today =
var _min = today()
return
CALCULATE(sum('Table'[Sales]), FILTER('Date','Date'[Date] >= year(today()) -3 && 'Date'[Date] <= year(today()) ) )Hi Amit,
Thanks for the response.
Before even data gets to visual, I want to filter the data in the data model layer (sorry if I use the wrong terms here but hope you get it right)
When I load the tables, I do not want all the data in the table(could be 20yrs data) to be loaded but only the rolling 3 years data.
I have a Roll3Yrs flag field in DIM_DateMap table. This field has value 1 for the last 3 years dates in date dimension.
I was hoping to filter this table with the filter condition Roll3Yrs = 1 and then inner join other tables to this table to filter data.Appreciate if you can guide me in achieving this.
Hi Anonymous
Go to Transform Data, click on the column header and change the filtering options. For more information, please read this article, https://radacad.com/only-get-the-last-few-periods-of-data-into-power-bi-using-power-query-filtering
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
