To Sort by FY

[Anonymous]

Hi,

Need your help regarding sorting, I want the months to sort from Apr to Mar (FY).

Please help me with the Dax.

TIA.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Try to create a sort column:

sort = IF(AND(


YEAR('Table'[Date])=YEAR(TODAY())-1 ,MONTH('Table'[Date])>3) ||

AND(

YEAR('Table'[Date])=YEAR(TODAY()) , MONTH('Table'[Date])<4),format(YEAR('Table'[Date])*100+MONTH('Table'[Date]),"000000"),RIGHT('Table'[FY],4))

Change the format of this column to whole number,then sort FY-Month by this column.

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

[Anonymous]

Thank you so much!! It worked!!

[Anonymous]

Hi,

Thank you for your quick response.

I have created the column

based on the columns below

FY_Month = IF(YEAR('Calendar'[Date]) = YEAR(TODAY()) || YEAR('Calendar'[Date]) = YEAR(TODAY())-1,'Calendar'[Test],

IF(YEAR('Calendar'[Date]) <> YEAR(TODAY()) || YEAR('Calendar'[Date]) <> YEAR(TODAY())-1,'Calendar'[FY]))

FY = IF(MONTH('Calendar'[Date])>3,YEAR('Calendar'[Date])&"-"&YEAR('Calendar'[Date])+1,YEAR('Calendar'[Date])-1&"-"&YEAR('Calendar'[Date]))

Test = CALCULATE(VALUES('Calendar'[Month-Year]),

FILTER(VALUES('Calendar'[Month-Year]),

AND(

YEAR('Calendar'[Date])=YEAR(TODAY())-1 , MONTH('Calendar'[Date])>3) ||

AND(

YEAR('Calendar'[Date])=YEAR(TODAY()) , MONTH('Calendar'[Date])<4)

))

So when I'm trying o use your query, it's not allowing me to do it.

Please Suggest.

TIA.

[ALLUREAN]

Hi, @Anonymous 

Please check this example:

https://www.dropbox.com/s/o20j4ej8t4kc0bv/FY-Month%20sort%28allure-analytics.com%29.pbix?dl=0

I suggest that you have a Calendar table with Date column (calendar date).

You should probably have Year column (number format) and Month number (1 for Jan, etc) - number format 

You need to create a Fiscal Year and Fiscal month number calculated columns based on this.

FiscalYear_4 =

IF('Calendar'[Monthnumber] < 4, 'Calendar'[Year],

'Calendar'[Year]+1) --Fiscal Year starting on 1st of April (Month End of 31 Mar)

FiscalMonth_4 =

IF((IF('Calendar'[Monthnumber]>4, ('Calendar'[Monthnumber])-3,9+('Calendar'[Monthnumber])))=13,1,(IF('Calendar'[Monthnumber]>4, ('Calendar'[Monthnumber])-3,9+('Calendar'[Monthnumber])))) --Fiscal Year starting on 1st of April (Month End of 31 Mar)

At the end you can combine both: FY-Month = 'Calendar'[FiscalYear_4] &"-"& 'Calendar'[CalendarMonth]

Then select FY-Month column and sort by FisclaMonth_4

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[amitchandak]

@Anonymous ,

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = if(month([Date]) <=4, (YEAR([Date]) -1)*100 +  month([Date]) +9, (YEAR([Date]) )*100 +  month([Date]) -3)

Sort month year of month year sort

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[Anonymous]

Hi,

Thank you for your response.

My FY_Month Column contains the following value and I want to sort that

It Includes the year too. Once the current FY ends it will change into 2021-22 and then it should start with Apr for the next year. That query is working fine, But I can't sort the month.

Please help.

TIA