how to sort quarters

[aliyahussein]

Hi, 

I would like to sort QRT as july-spet QTR 1, oct-dec QTR 2, jan-mar QTR 3 and apr-june QTR 4. Currently my visual goes QTR 4, QTR 2, QTR 3, QTR 1. 

Please advise. 

Thank you,

Aliya 

[v-alq-msft · Accepted solution]

Hi, @aliyahussein 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end. 

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

Calculated column:

FY QTR = 
var m = MONTH([Date])
var y = YEAR([Date])
return
IF(
    m<7,
    SWITCH(
        TRUE(),
        m in {1,2,3},"FY "&y-1&" QTR3",
        m in {4,5,6},"FY "&y-1&" QTR4"
    ),  
    SWITCH(
        TRUE(),
        m in {7,8,9},"FY "&y&" QTR1",
        m in {10,11,12},"FY "&y&" QTR2"
    )
)

FY QTR Value = 
var m = MONTH([Date])
var y = YEAR([Date])
return
IF(
    m<7,
    SWITCH(
        TRUE(),
        m in {1,2,3},(y-1)*10+3,
        m in {4,5,6},(y-1)*10+4
    ),  
    SWITCH(
        TRUE(),
        m in {7,8,9},y*10+1,
        m in {10,11,12},y*10+2
    )
)

There is a relationship between two tables. Then you need to make 'FY QTR' sorted by 'FY QTR Value'.

Result:

Best Regards

Allan

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

[aliyahussein]

You rock! thank you!!!!!

[AlB]

Hi @aliyahussein 

Check this out

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column#sort-using-the-sort-...