Forum Discussion
Filter Drop down
Hi,
I am trying to recreate a column chart within Power Bi. The aspect I'm having trouble with is that I must put in a drop down box that lets you filter the chart into 5 different aspects.
1. Last 30 days,
2. This month in days,
3. last 30 weeks,
4. Last 12 months,
5. Last 8 Quarters.
Can someone recommend an easy way of obtaining this drop down box within Power bi Desktop please?
Thanks
hey i tried to create a sample for you, see pbix file here
https://drive.google.com/file/d/0B8z5lc1oML-hT3pfMVI1OE5QRFU/view?usp=sharing
Basically adding some measures in calendar table and then filtered on those measures. once you have your data table related to calendar table, i expect it will filter record based on your slicer. anyhow this is an idea, you can change/amend as you see fit.
Let me know how it goes.
To filter the "Last X period" data, you can create a calculated table to include all "Last X periods" dates and add a column for tagging "Last X period". Then build the relationship to existing date table to filter corresponding dates. For more details, please refer to article below:
Power BI – Time Period Slicer for Last 7 Days,Last 30 Days..
Regards,
10 Replies
Can't think of anything off the top of my head. Any chance you can post some sample data so I can play around with the concept?
I wonder if you could create a column where you flagged those categories. The problem though is with overlapping buckets. Hmm...
Master Previous 30 Days This Month Previous 30 Weeks Previous 12 Months Previous 8 Quarters 25/04/2017 null 25/04/2017 null null null 24/04/2017 24/04/2017 24/04/2017 null null null 22/04/2017 22/04/2017 22/04/2017 22/04/2017 null null 21/04/2017 21/04/2017 21/04/2017 21/04/2017 null null 20/04/2017 20/04/2017 20/04/2017 20/04/2017 null null 19/04/2017 19/04/2017 19/04/2017 19/04/2017 null null 18/04/2017 18/04/2017 18/04/2017 18/04/2017 null null 15/04/2017 15/04/2017 15/04/2017 15/04/2017 null null 14/04/2017 14/04/2017 14/04/2017 14/04/2017 null null 13/04/2017 13/04/2017 13/04/2017 13/04/2017 null null 12/04/2017 12/04/2017 12/04/2017 12/04/2017 null null 11/04/2017 11/04/2017 11/04/2017 11/04/2017 null null 10/04/2017 10/04/2017 10/04/2017 10/04/2017 null null 09/04/2017 09/04/2017 09/04/2017 09/04/2017 null null 08/04/2017 08/04/2017 08/04/2017 08/04/2017 null null 07/04/2017 07/04/2017 07/04/2017 07/04/2017 null null 06/04/2017 06/04/2017 06/04/2017 06/04/2017 null null 05/04/2017 05/04/2017 05/04/2017 05/04/2017 null null 04/04/2017 04/04/2017 04/04/2017 04/04/2017 null null 03/04/2017 03/04/2017 03/04/2017 03/04/2017 null null
Hi Smoupre,
I have included some sample data above. I have filtered the data into the headings I would like.
Thanks for your help
OK, I'm a little confused by your data. I was expecting to have a table with a list of dates and some kind of value that you are aggregating. Is that not the case? You just want to be able to filter dates into buckets??
To filter the "Last X period" data, you can create a calculated table to include all "Last X periods" dates and add a column for tagging "Last X period". Then build the relationship to existing date table to filter corresponding dates. For more details, please refer to article below:
Power BI – Time Period Slicer for Last 7 Days,Last 30 Days..
Regards,
Thanksv-sihou-msft, I used another way of doing it but is always good to know different methods of completing it. Thanks you very much for your help.
Hi ecroke
I'm new to this too but if it were me I would try to use a 'dynamic' column, or several columns to identify when they fall into those catergories.
For example (Last 30 days):
Last 30 days = if(Table_A[Date Field] > (Today()- 30), "Last 30 Days", "")
*This, as far as I am aware, should return "Last 30 Days" where the date in questio nis greater than today -30, and therefore should capture anything that falls into that. As the data increases or the data changes records will fall in or out of this so will be effectively 'LIVE'.
You might be able to do it all in one expression:
if(Table_A[Date Field] >(Today () -30), "Last 30 Days", if(Table_A[Date Field] >(Today() -(30*7)), "Last 30 Weeks", if(Table_A[Date Field] >(Today() -365), "last 12 Months", if(Table_A[Date Field] >(Today()-730), "last 8 Quarters", ""))))
I might be a bit naive about the caluations in there and there might be better ways of perfecting those - but hopefully you can see the idea behind my thinking here??
