Forum Discussion
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.
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.
6 Replies
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 ,
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
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
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.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_4Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.
Appreciate your Kudos !!!
