Forum Discussion
Helper IIISort 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.
Help would be much appreciated.
Thanks
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)Sort by [QuarterNumber].
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.
4 Replies
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)Sort by [QuarterNumber].
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.
samioberoiHI 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
- samioberoi
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