How to sort by latest FY year in power bi using Matrix visual?

[Sampathkumar_v]

Hi Folks,

@v-jianboli-msft, @v-xinruzhu-msft 

I want my DU as in this same ascending sequence, but I want quarters to sort in the form of latest financial year. Let's say for example, there are 2 years in the visual and displayed in the form of quarters (Q1, Q2 and so on), values should be sorted as per 2023 current quarter i.e. Q2'23 but retaining the same DU sequence.

My DU column is coming from a different table and rest 3 columns are coming from the same table which has a one to many relationship among themselves.

I have tried this logic but no work around 

Sort by Latest year =

VAR _a =

    MAXX ( ALLSELECTED ( 'Base RCR ADT'[QTR] ), VALUE(RIGHT([QTR],2)))

VAR _b =

    SUMX (

        FILTER (

            ALL ( 'Base RCR ADT' ),

            'Base RCR ADT'[Grp Customer Name] = SELECTEDVALUE ( 'Base RCR ADT'[Grp Customer Name] )

                && VALUE(RIGHT([QTR],2))=_a

        ),

        [revenue in MN]

    )

RETURN

    IF ( ISINSCOPE ( 'Base RCR ADT'[QTR] ), ( [revenue in MN] ), _b )

[v-shex-msft]

Hi @Sampathkumar_v,

I'd like to suggest you refer to the following blog to create new table with fiscal year and index field as sort table. After these steps, you can create relationship to link raw field and new table and use new table field on your visual to enable custom sort orders.

Custom Sorting in Power BI  

Regards,

Xiaoxin Sheng

[Sampathkumar_v]

Hi @v-shex-msft ,

Based on about steps only I am able to do sory by BU column like BU1,BU2,BU3,ect... But my ask is here I want my DU as in this same ascending sequence, but I want quarters to sort in the form of latest financial year. Let's say for example, there are 2 years in the visual and displayed in the form of quarters (Q1, Q2 and so on), values should be sorted as per 2023 current quarter i.e. Q2'23 but retaining the same DU sequence.

[v-shex-msft]

Hi @Sampathkumar_v,

Can you please share some static dummy data that keep the raw data structure with expected results and pasted here with table format? It should help us clarify your scenario and test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Sampathkumar_v]

Hi @v-shex-msft ,

I am not able to attach any copy you to refer as I don't have admin permission I have attached the screen shot for reference.

I want my BU as in this same ascending sequence, but I want quarters to sort in the form of latest financial year 2014. Let's say for example, there are 4 years in the visual and displayed in the form of quarters (Q1, Q2 and so on), sales value should be sorted as per 2014 FY only and also when I do drill through to product name my sales should show's in Desc order as per values however still my retaining the same BU sequence.

I tried this below logic but my BU sequence get changed I want to retain my BU sequence as same order which is there in screen shot.

IF(ISINSCOPE('Table'[Column2]),RANKX(ALLSELECTED('Table'[Column2]),CALCULATE(SUM('Table'[Column3])),,DESC,Dense))