Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Sorting Dates

Hi,

 

Currently I am trying to sort a selection of dates from 2010-2015 into two categories.

 

The season it occurred eg 2011-2012 and then whether it is preseason, regular season or playoffs.

 

The end goal is have these categories as a slicers.

 

I am finding it difficult to set up calculated columns to achieve this.

 

Are there any other methods to do this?

 

Thanks in advance

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

     

    Hi v-jiascu-msft,

     

    I am sorting NBA dates data into season and season type.

     

    For the 2012-2013 season.

     

    Season Type = SWITCH(TRUE(),

'Merge1'[Date] >= DATE(2012,10,5) && 'Merge1'[Date] <= DATE(2012,10,29), "Preseason",

'Merge1'[Date] >= DATE(2012,10,30) && 'Merge1'[Date] <= DATE(2013,04,19), "Regular season",

'Merge1'[Date] >= DATE(2013,04,20) && 'Merge1'[Date] <= DATE(2013,06,5), "Playoffs",

'Merge1'[Date] >= DATE(2013,06,06) && 'Merge1'[Date] <= DATE(2013,06,20), "Finals",

'Merge1'[Date] >= DATE(2013,06,21) && 'Merge1'[Date] <= DATE(2013,10,4), "Offseason"

     

    I have 8 seasons of data.

     

    Each season has different starting dates for the preseason, regular season etc.

     

    Would I have to copy and alter this code for each of the 8 seasons .

     

    Or is there an alternative?

     

    Thanks in advance

8 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

     

    Best Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Lawrence,

     

    You can add a New Column in your table.

    For the first category you can use:

     

    Then Season = SWITCH(TRUE(),

                                  YourDateColumn > DATE(2011,6,31) && YourDateColumn < DATE(2012,03,01), "Season 2011-2012",

                                  YourDateColumn > DATE(2012,6,31) && YourDateColumn < DATE(2013,03,01), "Season 2012-2013",

                                  YourDateColumn > DATE(2013,6,31) && YourDateColumn < DATE(2014,03,01), "Season 2013-2014"

                                  )

     

    Replace YourDateColumn with the column you use

    My assumption is that your season begins in July and ends in February, just change those dates in the actual season dates

    Depending on your location settings use either , or ; as saperator

     

    Same goes for the category;

    For example 

    SeasonType = var dateWithoutYear = INT(FORMAT(YourDateColumn,"MMdd"))

                            return

                           SWITCH(TRUE(),

                          dateWithoutYear > 0501 && dateWithoutYear < 0531, "Preseason", 

                          dateWithoutYear > 0631 && dateWithoutYear < 0301), "Season",

                          dateWithoutYear > 0325 && dateWithoutYear < 0425, "Playoffs"

                           )

     

     

     

     

     

     

     

    Replace the start and end dates with the corect ones and make sure that if a period goes from december to january, you switch begin and ends like in the example

    the format is MMdd suh that 0503 is the third of May.

     

     

    Then click on the slicer icon and select a column. Do the same for the other one

     

    Hope it helps :)

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous

       

      Hi,

       

      Thanks for the response.

       

       

      When you mention "if a period goes from December to January, you switch begin and ends like in the example"

       

      Are you referring to this section

       

      dateWithoutYear > 0631 && dateWithoutYear < 0301), "Season",

       

       

      Also if I have five seasons of data and each season begins on a different date.

       

      Would I have to repeat the season type section five times to account for this ?

       

      Thanks,

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

         

        If the dates start from different dates without any rules, I'm afraid you have to repeat them. Can you share the details, please?

         

         

        Best Regards,