topic Re: Grouping multiple FY's in DAX Commands and Tips

Grouping multiple FY's

Tob_P — Sun, 14 May 2023 13:38:02 GMT

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

Re: Grouping multiple FY's

tamerj1 — Sun, 14 May 2023 15:28:40 GMT

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

Re: Grouping multiple FY's

Tob_P — Sun, 14 May 2023 15:36:29 GMT

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.

Re: Grouping multiple FY's

tamerj1 — Sun, 14 May 2023 16:01:14 GMT

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
)

Re: Grouping multiple FY's

Tob_P — Sun, 14 May 2023 18:17:49 GMT

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.

Re: Grouping multiple FY's

tamerj1 — Sun, 14 May 2023 18:42:50 GMT

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.