How do I limit the available months per year
I’m trying to maintain two columns (2024 and 2025) on my Discover Matrix Grid. However, I only want the 2024 column to reflect data from July 1 to December 31, while the 2025 column should include all available data.
I understand that I could select full months (e.g., July 2024, August 2024, etc.) to achieve this, but I’d prefer the column headers to remain simply labeled as "2024" and "2025", rather than individual months.
Additionally, I'd like the MONTH slicer to only display July through December if 2024 is selected in the YEAR slicer.
11 replies
-
Hi ,
You leave the Year and Month Name attributes and create a custom list for Full Month which has July 24 onwards, and drop it into the "combined elements" filter.
Here I did a full months set from July 2009 to July 2010:
For the slicers, you can create a parameter of years, and then create a custom list for months that looks something like this (using that year parameter you created) :
if( [Date].[year].@[908dc051-227d-4bbf-a74f-ee31d15c124e].caption = "2009", range( [Date].[Month name].[July], [Date].[Month name].[December] ), [Date].[Month name].allmembers )and then you can use this list to create month parameter, or select it into the filters drop zone:
-
Thanks; but I don't want the months to show on the Matrix Grid—only the years.
-
Oh, then just remove the month name. You'll have the years displayed and the values will be filtered down to the months that you have in full months list.
-
The issue is that the full months list goes back to 2003. I want to be able to isolate a specific portion of that list and I have no access to alter the model.
-
I'm sorry, I think I'm loosing track of what you are trying to do exactly.
Maybe explain in more details, what exactly the grid should look like and what exact slicers do you want to have.
Which attributes + what data do you have in the model (I understand you have years, month name and full month all going back to 2003, right? but you want to see only July 24 and after? Is this going to change dynamically?)
Are the 2 original questions meant to be within the same report? (year is both a slicer and the columns of the matrix grid?) -
see the image attached.
-
Is the user meant to chose 1 year and 1 month? And the grid will be filtered down to the values of the selected year+month?
And the header should display what year was selected, but not the month?
So it should look like this (plus the fact that the months in the slicer are dynamic based on the year) :
-
Yes; but it should be a multi-select where the months shown for 2024 are only (July, August, September, October, November, December) and the months shown for 2025 are only (January, February, March, April, May, June)
-
So do as I wrote in the last section of the original comment:
1 - create a year parameter (you can, in the parameter setup itself, specify you want 24-25 only, and not all years)
2 - create the custom list of the months, as written in the comment (you can limit as another range instead of all the months for 2025)
then:
3 - select the year parameter on the columns
4 - select the custom list as a multi-select filter -
Ok - works... kind of.
When I choose 2024, it shows the values of July-December (Great)
When I choose 2025, it shows the values of all months (Great)
When I choose 2024+2025 it shows only the values of July-December (not great). When both are chosen it should show the values of July-December for 2024, and all months for 2025.
I can feel it... so close! And thanks so much for getting me this far
-
Glad to help!
Add a check for multi-selection of the parameter inside the IF condition:If( {[Custom Query].[Order Invoice Date year].@[....id....]}.Count == 1 && [Custom Query].[Order Invoice Date year].@[....id....].Caption = "2024", Range(...), All...)
-