Forum Discussion

Conodon's avatar
Conodon
Frequent Visitor
3 years ago
Solved

Seasonal Date Ranges (1 Sep - 30 April every year)

Hi all,

 

I have a dataset that contains outcomes that occur seasonally between 1 September and 30 April (inclusive) every year since 2019 and will continue every year going forward. I am wondering if there is a way I could create a new column in my date table that would say something along the lines of:

 

Month | Year | Season (new column)

Aug | 2019 | Out of season

Sep | 2019 | Season 2019-20

Oct | 2019 | Season 2019-20

Nov | 2019 | Season 2019-20

Dec | 2019 | Season 2019-20

Jan | 2020 | Season 2019-20
Feb | 2020 | Season 2019-20

...

Aug | 2020 | Out of season

Sep | 2020 | Season 2020-21

Oct | 2020 | Season 2020-21

Nov | 2020 | Season 2020-21

Dec | 2020 | Season 2020-21

Jan | 2021 | Season 2020-21
Feb | 2021 | Season 2020-21

and so on... 

 

I'm hoping there is an easy way to figure this out, I could only think of making a conditional column for every month but that would take me a while.

 

Thanks in advance 

Need Help
  • Conodon's avatar
    Conodon
    3 years ago

    I think I have figured this out by creating a column with the following DAX formula 

     

    Seasonal = SWITCH(TRUE(),Dates[Date] >= DATE(2019,09,01) && Dates[Date] <= DATE(2020,04,30),"Season 19-20",Dates[Date] >= DATE(2020,09,01) && Dates[Date] <= DATE(2021,04,30),"Season 20-21",Dates[Date] >= DATE(2021,09,01) && Dates[Date] <= DATE(2022,04,30),"Season 21-22",Dates[Date] >= DATE(2022,09,01) && Dates[Date] <= DATE(2023,04,30),"Season 22-23",Dates[Date] >= DATE(2023,09,01) && Dates[Date] <= DATE(2024,04,30),"Season 23-24","Not in season")

4 Replies

  • Conodon's avatar
    Conodon
    Frequent Visitor
    3 years ago

    I think I have figured this out by creating a column with the following DAX formula 

     

    Seasonal = SWITCH(TRUE(),Dates[Date] >= DATE(2019,09,01) && Dates[Date] <= DATE(2020,04,30),"Season 19-20",Dates[Date] >= DATE(2020,09,01) && Dates[Date] <= DATE(2021,04,30),"Season 20-21",Dates[Date] >= DATE(2021,09,01) && Dates[Date] <= DATE(2022,04,30),"Season 21-22",Dates[Date] >= DATE(2022,09,01) && Dates[Date] <= DATE(2023,04,30),"Season 22-23",Dates[Date] >= DATE(2023,09,01) && Dates[Date] <= DATE(2024,04,30),"Season 23-24","Not in season")
    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      3 years ago

      Hi Conodon ,

       

      That's one way to do it. But if this is going to happen as new periods are  added, you might want to follow a more dynamic approach.