Forum Discussion

Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
3 years ago

Grouping 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...
"Calculated Column"
Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
3 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    Tob_P
    Icon for Helper V rankHelper V
    3 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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.