Forum Discussion

samioberoi's avatar
samioberoi
Icon for Helper III rankHelper III
3 years ago
Solved

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 st...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

  • samioberoi's avatar
    samioberoi
    Icon for Helper III rankHelper III
    3 years ago

    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