Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

To Sort by FY

Hi, Need your help regarding sorting, I want the months to sort from Apr to Mar (FY). Please help me with the Dax. TIA.
Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
ALLUREAN
Solution Sage
4 years ago

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/