Forum Discussion
Linked Slicers/Parameters for Filtering
Good morning,
I'm currently working on a sales report that requires date filtering to be able to view historic sales data. Each entry in my table for sales has a sales credit date which is what this would be driven off of. I'd like to be able to have two slicers, where one drives the data available for selection in another. Let's say slicer 1 drives the data for slicer 2. In slicer 1, we can select the quarter. In slicer 2 we can select a date range. So if I were to select Q1 in slicer 1, the date range in slicer 2 would be 1/1/2022-3/31/2022. If I selected Q2 in slicer 1, the date range in slicer 2 would change to 4/1/2022-5/19/2022 since that is the latest date available in my data.
I've tried using parameters to drive the quarter and date selection, which do work to an extent, but I can't change change the date dynamically. I had a measure that figured out which quarter was selected, if any, which then drove the maximum date of the other parameter. Here are the formulas that drove the maximum date:
MaxDate1 =CALCULATE(max(Daily[Credit Date]),filter(Daily,QUARTER(Daily[Credit Date])=[CurrentQtr]))
MaxDate2 =CALCULATE(max(Daily[Credit Date]),filter(Daily,QUARTER(Daily[Credit Date])=1))
MaxDate1 defaulted to 5/19/2022 in the date selection parameter, regardless of what quarter was selected. I had cards set up where I could view the actual values of those measures and they would change correctly based on input. If I selected Q1, [CurrentQtr] would change to 1, and MaxDate1 would change to 3/31/2022. I then hardcoded the quarter number as you can see in MaxDate2 which would change the maximum date in the date selection parameter, but it's hard coded... I tried a whole number of workarounds for this including if/then statements, variables, etc. I am unable to use a measure of any sort to filter based on quarter.
If anyone can provide me with some information on how to resolve this, it would be much appreciated. If you require more information, ask away. Thank you.
2 Replies
- amitchandakSuper User
Anonymous , do you want two slicers?
How to use two Date/Period slicers
- AnonymousNot applicable
amitchandak Yes, but not quite how you have in the video. Using examples from the first paragraph of my post, slicer 1 would contain quarter numbers. Slicer 2 would contain a date range of sales dates from my data set controlled using a slider. The dates available in slicer 2 would be driven off of whichever quarter is selected in slicer 1. So if I had Q1 selected in slicer 1, the date range in slicer 2 would be 1/1/2022-3/31/2022. If I selected Q2 in slicer 1, the date range in slicer 2 would then be 4/1/2022-5/19/2022 since 5/19/2022 is the latest date in my data.