Grouping multiple FY's

[Tob_P]

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"?

[Tob_P]

Hi @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]

@Tob_P 

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.

[tamerj1]

@Tob_P 

Would you please clarify the a little furthermore? You can use this as a slicer and you can use it in the visual itself. You can also filter any measure by filtering by the selected fiscal years in the Groups table. Not sure what your real case is. 

[tamerj1]

Hi @Tob_P 

try to add a calculated column 

Group =
IF (
'Table'[Financial Year (SHM)]
IN { "FY 2020/2021", "FY 2021/2022", "FY 2022/2023" },
"Last 3 Years",
"Non Covid"
)