Use two Drop down slicers as FROM /TO filter dates

[hashanr]

Hello everyone,

I have this requiremnet where there is two dropdown slicers used to select from/to period. These drop down contains period in [Month] [Year] format and not in proper date format. See below screenshot,

My target is to filter my [Date] column in calander table based on these two From/To Drop Down slicers. Field used to populate drop down slicers is [Period] column in same calander table. Here is my calander table below, 

According to requirement, user want to see at least one year of data. ex : April 2019 (From Slicer) / March 2020 (To Slicer). And calander table is linked to other tables so if filtaration works that should filter rest of data 

Appreciate any support on this since i'm really stuck. Thanks in advance.

Hashan

[v-lili6-msft]

hi  @hashanr 

I would suggest you createa  YearMonth (numeric) column and use it as a slicer between type

Otherwise, you need two separate table and create measure and use visual level filter as a workaround to get it, but this is much more complicated.

Regards,

Lin

[AllisonKennedy]

@hashanr  Because the period column is in the same table you want to filter, you will not be able to use two drop down slicers as is.

You will first need to create two additional UNRELATED tables for

PeriodStart = VALUES(Date[Period])

and 

PeriodEnd = VALUES(Date[Period])

Make sure they are not related to the Date table in the model.

Then use DATESBETWEEN(Date[Date], SELECTEDVALUE(PeriodStart[Period], MIN(Date[Date])), SELECTEDVALUE(PeriodEnd[Period]), MAX(Date[Date]))) as a filter in a CALCULATE expression. 

You'll have to apply this measure as a visual level filter to every visual on the page, but at least it will meet client requirements. 

Others may have more elegant solutions for you. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Greg_Deckler]

@hashanr I am going to have to generally agree with @AllisonKennedy here in terms of the general approach. But since you reached out to me personally and asked me to take a look, I will provide my thoughts. Generally if I had this kind of requirement, I would approach it with the two disconnected date tables and generally go the same route that has been suggested previously. I call these sorts of things "Complex Selectors" and they have a wide variety of forms but they all end with a measure returning 0 or 1 as to whether or not to display something. So, like:

Measure =

  VAR __From = MAX('Slicer1'[Date])

  VAR __To = MAX('Slicer2'[Date])

RETURN

  IF('Table'[Date] >= __From && 'Table'[Date] <= __To, 1, 0)

You would then use this in your Filter pane to determine which rows in your 'Table' to display (the 1's). This probably looks pretty familiar.

Now, your next requirement is to make sure that other related tables are also filtered. In this, @amitchandak 's approach is by far the easiest and just works natively. I would first go back to the customer and show them this approach and explain that this works natively and anything else is kind of going to be a hack. I have found most customers are pretty reasonable. In the unfortunate case that you have an unreasonable customer, there are a few options.

First, if you put values from other tables in the table that is filtered by our rather hacky complex measure, they should be filtered appropriately. Where you may run into trouble are some measures I suppose but those are rather hard to predict because we do not know wha those measures might be or what columns from other tables you wish to display. You may have to end up creating new measures that go like this:

Measure =

  VAR __From = MAX('Slicer1'[Date])

  VAR __To = MAX('Slicer2'[Date])

  VAR __Table = FILTER('Table', 'Table'[Date] >= __From && 'Table'[Date] <= __To)

RETURN

  <Some kind of operations over __Table, maybe ADDCOLUMNS and use measures/columns from other tables, etc. >

Again, very difficult to be specific. But, instead of us guessing, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[amitchandak]

@hashanr , Refer id this article can help. In place of the date, you can have your choice of the slicer

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[hashanr]

@amitchandak I wish i could use slicers like your article but user strickly wanted two dropdown slicers to pick periods. Anyway thanks for your attention and support

[amitchandak]

@hashanr , you may have created two independent date/month tables and choose a month from those as from and 2 and create a measure like

Measure = 
var _min =maxx(ALLSELECTED('Date1'),'Date1'[Date])
var _max =maxx(ALLSELECTED('Date2'),'Date1'[Date])
return
CALCULATE(SUM('order'[Qty]),filter(all('Date'),'Date'[Date]>=_min && 'Date'[Date]<=_max))

[hashanr]

Hi Thank you for your input. I got answers with similar solution from few of members. All considered, I managed to overcome challange. thank you for your support !

[hashanr]

Hi Thank you for your input. I got answers with similar solution from few of members. All considered, I managed to overcome challange. thank you for your support !

[Surekha]

Hi, could you please share how it got resolved. Would be helpful. Looking for similar solution.