Finicky Fiscal Year Sorting

[FrugalEconomist]

Dear Power BI Community,

As always, thank you for your helpful responses.

I'm having trouble now presenting my data in a custom end of year. I believe my DAX calculated formula is correct, but the axis is still in the default order.

How can I get my axis months to sort according to my custom end of year of September 1st?

My calendar table is a simple:

Calendar = Calenday("01-01-2010", Today())

Thank you

[andre]

The best way to solve this reliably is to create a new column and call it FiscalDate, then you can use the DATEADD function to shift it the required number of periods so that the Calendar date of 9/1/2016 is corresponding with a Fiscal Date 1/1/2017.  Once that's done, you can create Fiscal Period, Fiscal Qtr and Fiscal Year columns and use those for reporting.  You can use your calendar date for relationships.

[adamh1]

Hi Guys - I have the same issue, We have a fiscal year Jul to Jun,  - in the format tab I'm nort able to sort graphs of tables by the sequence of Months form Jul to Jun

In trying to fix/resolve this  in the data tab I've tried 3 differnt columns for the financial year period numbers i.e.

i. FinMonthSort = if(Month(DateKey[date])>6,Month(DateKey[date])-6,Month(DateKey[Date])+6)

ii.FMonthNo = SWITCH(DateKey[MonthNo],    1,"7",    2, "8",    3, "9",    4, "10",    5, "11",    6, "12",    7, "1",    8, "2",    9, "3",    10, "4",    11, "5",    12, "6",  ( deriving financial month form calander month using switch)
    BLANK ())

iii.FinDate = DATEADD(DateKey[Date],+6,MONTH) and derived the Financial year period number from the FinDate

- when sorting each financial month column in the data tab the sequence of the table contents dones't change when clicking on the sort icon

- the table only sorts when sorting the column directly i.e. select the column and right click and select "Sort in asending order"... (don't know if this a the problem??) 

When I go to the format tab the changes from sorting don't flow thru - the month short name "MMM" continues to be sorted alphabetically 

any ideas or further information I could provide to help solve please let me know.

many thanks Adam

[a68tbird]

This might not be the most elegant solution, but I think it will work.  

First, extract the month number from your date in your Calendar table (Date.Month([Date])).  Then create a custom column in your calendar table that will set a custom sort order:

=if [Month Number] >= 9
then ([Month Number] - 9)
else ([Month Number] + 3)

Finally, in Data Table view, under the Modeling tab, use Sort by Column and select your new custom sort order. 

I believe this should work...haven't actually tested in an application. 

[FrugalEconomist]

Thanks a68tbird.

Thanks for your feedback, but unfortunately it doesn't work because the 'canned' power bi date hierarchy can drill down date type fields into Year, Quarter, Month, and Day. However when I create custom columns, even though I've made the date to date join, it doesn't recognize any of the calcluated columns or measures for some reason 😞

[a68tbird]

How about in the visulization settings. Under the X-Axis setting, you can set Start and End dates. 

[FrugalEconomist]

What!?!? That's so cool, but it doesn't show up for me 😞

I also cannot change the data type to continuous.