Start date range at Oct and DISPLAY based on Fiscal Year

[nitricpyro]

I'm sorry but I've looked and looked; please excuse me if this is redundant. 

I have a project where the data is in calendar years, my date table is in calendar dates etc. but I need to report on Fiscal year. 

Ex:

10/2021 = Period 1, Q1, FY22

11/2021 = Period 2, Q1, FY22

etc. etc. 

9/2022 = Period 12, Q4, FY22

...

I've tried everything to get things to line up... I've added a sort column, a second dates table and linked in to the first table, custom sorts etc. 

Is there truly no way to simply tell Power BI Desktop to "Start with October, then November, then Dec, then Jan... etc."

[v-shex-msft]

Hi @nitricpyro,

You can take a look at the following link to create a custom sort table if helps:

Custom Sorting in Power BI 
Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

In the Dates table, write calculated column formulas to extract Month Name and Month number.  Create a seperate 2 column table (name it as Month order).  In the first column of the Month order table, write down all the months and in the second column assign numbers to tell the order of the months - so Octoboer should have 1, November 2 and so on.  Build a relationship from the Month name column of the Dates table to the Month name column of the Month order table.  In the Dates Table, write a calculated column formula to extract Order: Order = related('Month order'[Order]).  In the Dates Table, sort the Month name by the Order column.  Write this calculated column formula to get the Financial Year

Financial Year = if(Date[Month number]>=10,year(Date[Date])&"-" &year(Date[Date])+1,year(Date[Date])-1&"-" &year(Date[Date]))

Hope this helps.

[lbendlin]

There is a way, but only for some functions. 

TOTALYTD function (DAX) - DAX | Microsoft Docs  - note that you can specify the END date of the fiscal year, in your case "09/30" or "30/09"  depending on your locale)

It won't work for fiscal quarters, for example.

"my date table is in calendar dates" - what prevents you from adding the fiscal calendar columns (Fiscal Year, Fiscal Quarter, Fiscal Month) ?