Filter Drop down

[ecroke]

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

[parry2k · Accepted solution]

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. 

[Back2Basics]

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??

[v-sihou-msft · Accepted solution]

@ecroke

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,

[ecroke]

Thanks@v-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.

[Greg_Deckler]

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...

[ecroke]

Hi Smoupre,

I have included some sample data above. I have filtered the data into the headings I would like. 

Thanks for your help

[Greg_Deckler]

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??

[ecroke]

Sorry for the confusion, I'm trying to replicate this dashboard.

[parry2k · Accepted solution]

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. 

[ecroke]

@parry2k

Thanks for your help. This works perfectly for me. 

[ecroke]

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