cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

 

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

View solution in original post

8 REPLIES 8
v-jiascu-msft
Microsoft
Microsoft

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jeroenvos
Frequent Visitor

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
Not applicable

@Jeroenvos

 

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,

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

 

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

View solution in original post

Hi @Anonymous

 

Thanks for your example. If those dates aren't stores somewhere or there is no specific logic, I'm afraid that you indeed have to assign that for each season.

 

But if you can find a table (on the web for example?) we can relate those dates to your table and categorize based on that.




Did I answer your question? Mark my post as a solution

Anonymous
Not applicable

Hi @Jeroenvos ,

 

Thanks for your help,

 

I will try to find a table that includes these dates.

 

Hi @Anonymous

More than welcome!

Just @mention me here if you need further help.







Did I answer your question? Then please consider 'Accept it as the solution' to help the other members find it more quickly. 

 



Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.