how to sort quarters

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, ...

Need Help

v-alq-msft

5 years ago

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'.