Swimming Season Filter

[AmazingRandom]

I have been asked if it is possible to create a filter based on swimming seasons used by our client (from Nov to April) and I wonder if something like that is possible. Within our data we have the years and dates, but they were looking to have a slicer that they could select for each season, say Swimming Season 23/24 (1/Nov/23 to 30/Apr/24) and so on. I have never done something like this so I am unsure as to if it is possible and how to get started with it, thanks in advance for the help 🙂

[hnguy71 · Accepted solution]

Hi @AmazingRandom 

Yes, that's very possible. Typically I would create a disconnected table to grab user inputs. As an example, you would build a similar table to this:

SwimSeasons = 

DATATABLE(
            "Season", STRING,
            "Start", DATETIME,
            "End", DATETIME,
            {
                {
                    "Swim 23/24", "11/1/2023", "4/30/2024"
                },
                {
                    "Swim 24/25", "11/1/2024", "4/30/2025"
                }
            }
)

 
Although you may want to make it a bit more dynamic but you'll need at least 3 columns. The season name, season start and end dates.

Then, all that's left is to retrieve the season start and end dates in your analysis.

SeasonStart = 

// Retrieve current season in scope
VAR _Season = SELECTEDVALUE(SwimSeasons[Season], MAX(SwimSeasons[Season]))

// Retrieve start date
VAR _Start = CALCULATE(MAX(SwimSeasons[Start]), SwimSeasons[Season] = _Season)

// Retrieve end date
VAR _End = CALCULATE(MAX(SwimSeasons[End]), SwimSeasons[Season] = _Season)

RETURN

// debug result. Use start and end date in analysis
_Season & " | " & _Start & " | " & _End

The dates that you return would then be the ones you would want to query against your date table:

[rohit1991]

To create a swimming season filter in Power BI:

1. Define Swimming Seasons:
   Assign seasons based on dates, e.g., 1 Nov 2023 to 30 Apr 2024 for 23/24.

2. Create a Calculated Column (DAX):

Swimming Season = 
IF(
    MONTH([Date]) >= 11, 
    FORMAT(YEAR([Date]), "00") & "/" & FORMAT(YEAR([Date]) + 1, "00"), 
    FORMAT(YEAR([Date]) - 1, "00") & "/" & FORMAT(YEAR([Date]), "00")
)

• Add a Slicer:
  Use the Swimming Season column in a slicer visual.

• Reference: Microsoft Power BI DAX Calculated Columns

[v-jingzhan-msft · Accepted solution]

Hi @AmazingRandom 

Do you have a Date or Calendar table in the model? If not, I'd suggest adding a Date table to the model. Create a relationship on Date column between the Date table and the Fact table. Then in the Date table, add a "Season" column with below DAX. Place the Season column into a slicer. Now you will be able to filter data with this slicer based on Seasons. 

Season = 
VAR vMonth = MONTH([Date])
VAR vYear = FORMAT([Date], "yy")
RETURN
SWITCH(TRUE(),
vMonth>=11, vYear & "/" & vyear+1,
vMonth<=4, vYear-1 & "/" & vYear,
BLANK()
)

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[hnguy71 · Accepted solution]

Hi @AmazingRandom 

Yes, that's very possible. Typically I would create a disconnected table to grab user inputs. As an example, you would build a similar table to this:

SwimSeasons = 

DATATABLE(
            "Season", STRING,
            "Start", DATETIME,
            "End", DATETIME,
            {
                {
                    "Swim 23/24", "11/1/2023", "4/30/2024"
                },
                {
                    "Swim 24/25", "11/1/2024", "4/30/2025"
                }
            }
)

 
Although you may want to make it a bit more dynamic but you'll need at least 3 columns. The season name, season start and end dates.

Then, all that's left is to retrieve the season start and end dates in your analysis.

SeasonStart = 

// Retrieve current season in scope
VAR _Season = SELECTEDVALUE(SwimSeasons[Season], MAX(SwimSeasons[Season]))

// Retrieve start date
VAR _Start = CALCULATE(MAX(SwimSeasons[Start]), SwimSeasons[Season] = _Season)

// Retrieve end date
VAR _End = CALCULATE(MAX(SwimSeasons[End]), SwimSeasons[Season] = _Season)

RETURN

// debug result. Use start and end date in analysis
_Season & " | " & _Start & " | " & _End

The dates that you return would then be the ones you would want to query against your date table: