How can I make my date slicer to work dynamically?

[Anmolgan]

Is it possible to make a date slicer which change it self when i select a year? or a quater? Suppose I select a quater or a year or a month in my different slicers, the date period should show me all the dates between all those periods? is it possible in PowerBI to do this?

[Cmcmahan]

Yes, it should happen automatically.  As long as they're slicing on the same table or related tables, slicers filter each other by default, unless you specify otherwise.

[Anmolgan]

I have built out the relationships among the 3 tables, but how can I specify this specifically to the date slicer that it needs to sort out.

[Cmcmahan]

Is the date slicer running against a fact table or a date table?

Can you share the .pbix with us?  There are a lot of ways to set up relationships between 3 tables and slicers among those.  

[Anmolgan]

I have just sent you the pbix via onedrive link, please check your private messages.

[Anmolgan]

sure I can let me send you the one drive link for the report.

[Cmcmahan]

It appears to be working for me.  When you select a Quarter, it restricts the available Months accordingly, and vice versa. If you want your Financial Year slicer to do the same, you should create it as a calculated column on the Fiscal Calendar table instead of the Master Sales Report table. Right now the Fiscal Calendar slices the Master Sales Report, but the Master Sales Report doesn't filter the Fiscal Calendar.

I'm curious as to why you have two date dimensions.  From a data modeling standpoint, it's much easier to just have your Fiscal Calendar table, and use that as the date dimension instead of having one with fiscal information and one with basic calendar information.  Any fiscal-specific columns can be ignored for non-fiscal calculations, and you aren't having to juggle two tables that share 6+ columns.

Think of it like this: any attributes that can be applied to a single date should go into one date table. If provided with a date, I should be able to go to one table, find the specified date, and then in that row see all the attributes of that date. 

For example, I can say that July 4, 2019 has the attributes Year:2019, Short Year:19, Day of Week:4, Day Name:Thursday, Full Fiscal Month:04, Fiscal Year:2020, Quarter:Q3, Quarter of Fiscal Year: 2, Financial Year: FY19/20, IsPublicHoliday: Yes, etc.  Now make a whole table of that, and you have a workable date dimension.

[Anmolgan]

If you build the date slicer with actual daily dates then that will not give you limited slicer when you select a quater, I want the actual date slicer to get reduced accordingly is that possible?

[Cmcmahan]

Yes, this is because you have the Dates table seperate from the Fiscal Calendar table.  There is no direct relationship between the two. I would highly suggest combining the two date tables into one, unless there is a hyper specific reason to not.  See my previous post about why this makes sense. 

When I create a date slicer based on the FullAlternateDateKey from your Fiscal Calender table (NOT the Dates table), it does get filtered based on other slicer selections.  Note that I also moved your Financial Year calculated column into the Fiscal Calendar table.

I've only made slicer selections for Financial Year and Month of Fiscal Year, but the date slicer only shows options in July 2018, since those are the only possible dates that fall within FY18/19 and the month of July.  

[Anmolgan]

But this did not work when you choose date slider, see a table in master Sales Report query that says calender key, if you took that only you and select years or quaters from dates table only, you will see that even though those have direct realtionship with each other but still selecting quaters wont reduce my slider in date slicer.

[Cmcmahan]

Ahhhhh, I understand the issue.

Sliding slicers act differently than dropdown or checkbox sliders by default.  They are automatically set up to ignore other slicers.  But you can turn that on.  I found out about this in this post. Use the Edit Interactions view in the Format tab, and set the other slicer selections to filter the range slicer.

When you first click on each of the yellow slicers, the icon circled in red is selected.  Change them to the slicer icon on the left, and the date slider will respect the others.

[Anmolgan]

I dont see the edit interaction feature in my case what I need to do in order to turn that on?

[v-diye-msft · Accepted solution]

Hi @Anmolgan ,

You can find the option here: 

Detailed usage refer to the official document: https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

[Cmcmahan]

It should be available under the Format tab on the far left. The format tab should be available when you select one of the slicer visuals.  Can you send a screenshot of what you see?

[Anmolgan]

Ok let me check that.