Forum Discussion
Finicky Fiscal Year Sorting
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.
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