Forum Discussion
Helper VGrouping multiple FY's
Having abrainmelt this Sunday - I've done something similar before but completely forgotten how to do it. I want to group Financial Years together, essentially the last 3 FY's and 3 other years which I'm classing as Non Covid years - can't use groups, as one of the FY's is in both categories. FY column is text
How can I get a calculated column that gives me the option to use in a slicer, that groups full financial years 20/21, 21/22 & 22/33 as "Last 3 years" and groups 18/19, 19/20 & 22/23 as "Non Covid"?
5 Replies
Tob_PHi tamerj1
I had something similar, but that won't work as 2022/2023 falls under both Last 3 years and Non Covid - with your suggestion, that will default to last 3 years - I don't even think it's possible for a calculated column to output two different scenarios. Really struggling to think of a solution for this, although on paper, it seems a fairly straightforward scenario.
- tamerj1
Community Champion
In this case the only solution is to have the group column in a disconnected two columns independent table that contains 3 rows of each group name, a column for the group name (Groups[Group]) and a column for the fiscal year (Groups[FY]). Then you can create a visual level filter measure which you need to place in the filter pane of the visual, select "is not blank" then apply the filter
FilterMeasure =
IF (
HASONEVALUE ( Groups[Group] ),
COUNTROWS (
FILTER ( 'Table', 'Table'[Financial Year (SHM)] IN VALUES ( Groups[FY] ) )
),
1
)- Tob_P
Thanks for the insight tamerj1
Unfortunately this is something that I need as a slicer so the measure would work, but doesn't get me what I need. I think I will just amend my existing measures, and have one that includes the dates for Non COVID and another that looks for the last 3 FY.
Thanks again.
