Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
samioberoi
Helper I
Helper I

Sort quarters as per academic year

Hi 

 

I want to sort Sep-Nov as Q1, Dec-Feb as Q2 so on and so forth. I managed to add a calculated column changing September to Month number 1, Oct to Month number 2 etc. & then used the switch statement to put September, October, November, which i changed to month numbers 1,2,3 and put that in Quarter 1. Finally, i used the switch statement again to name Q1 as Sep-Nov and Q2  as Dec-Feb and Q3 as Mar-May and Q4 as Jun-Aug. Now when i put quarter name column as Sep-Nov, Dec-Feb, Mar-May, Jun-Aug into visual it comes in a different sorting order, while as i want Sep-Nov coming in the first column, Dec-Feb in the 2nd column and so on. 

20230104_152201.jpg

Help would be much appreciated.

 

Thanks

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @samioberoi ,

 

You should sort your text range column by a numeric column.

You can create a numeric column like this.

QuarterNumber = SWITCH(MONTH([Date]),9,1,10,1,11,1,12,2,1,2,2,2,3,3,4,3,5,3,6,4,7,4,8,4)

vstephenmsft_0-1672899424005.png

Sort by [QuarterNumber].

vstephenmsft_1-1672899432966.png

vstephenmsft_2-1672899490119.png

You can download my attchment for more details.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-stephen-msft
Community Support
Community Support

Hi @samioberoi ,

 

You should sort your text range column by a numeric column.

You can create a numeric column like this.

QuarterNumber = SWITCH(MONTH([Date]),9,1,10,1,11,1,12,2,1,2,2,2,3,3,4,3,5,3,6,4,7,4,8,4)

vstephenmsft_0-1672899424005.png

Sort by [QuarterNumber].

vstephenmsft_1-1672899432966.png

vstephenmsft_2-1672899490119.png

You can download my attchment for more details.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HI Stephen,

 

 It seems to be working fine. Thanks for help. Just one quick question please. If the calendar year shows the count of customers by normal calendar quarters like Jan-Mar, Apr-Jun etc. and rather than using that calendar quarter i want PBI to start counting the first quarter from Sep - Nov, Dec- Jan etc. So, i don't want PBI to count the third quarter of calendar year July, August and September and giving me the count of employees in that quarter e.g. 3000; however i want PBI to count Sep, Oct and Nov as first quarter and should count the customers for those 3 months and so on. The reason i am asking is that i had created a Date dimension table earlier, since our data file had inconsistent dates and when i linked that Date dimension table with our data file and counted that as per normal calendar quarters, it counts it as e.g. Jan-Mar 1000 customers, Apr-Jun 2000 customers, July- Sep 3000 customers and when i changed it on the date dimension table the first quarter as Sep-Nov it just gives me same count figure as it was giving me for Q2 (Apr-Jun) in the date dimension table earlier.

Hope i could explain it properly rather than confusing it.

Thanks again for your help for the previous query & looking forward for the reply on this one.

Regards

Sam

TomásFonseca
Regular Visitor

Have you sorted the final column by the one you previous created (the one that as the quarters as numbers)?

Hi

Yes, i did sort the final column by the previoulsy created column giving the column number. It still shows the columns in the wrong order.

 

Thanks

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.