Forum Discussion
Fiscal Year and Month Order
- 9 years ago
Pammy2411 please see the linked .pbix file. Then check out the 3 steps in the query editor starting with "CC:". These are the custom columns i created using the code in my original post.
- The 1st column calculates the Month Number using the 3-letter Month abbreviation found in the [Month] column.
- The 2nd column calculates the Fiscal Year based on a July start. (eg. 2014 June is FY 2014, but 2014 July is FY 2015)
- The 3rd column calculates the Fiscal Month # based on a July start.
- New columns types are changed to Whole Numbers
- The table is then sorted ascending by Fiscal Year and then Fiscal Month giving you the order you are looking for
- An index column was added to provide values for the visualization on the report
The line+bar combo graph is created using Fiscal Month and Fiscal Year along the X-axis. The chart is then Drilled Down to display the order you are looking for.
EDIT: ALSO, if you wish to display [Month] "Jan" "Feb" etc... in the visualization and still have the correct fiscal month sort applied, in the "Modeling" tab with the [Month] field selected from your fields list select "Sort this Column by" and then [Fiscal Month] to retain the fiscal month order.M script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdCxCsIwGEXhV5HMHVS8DyCIQ0EdHEuHWoJLqUWM0LdvfzTJyVJuOYSEr2ncfrs7uMrV3bh+7Wdjs61SOftHLDZRLt07FpsoxykVm8WZOZ+ZWeqQXxDGsgy5DMU94ZnuWSfK3U+x2ES59Z9YbKJcX99YbKKcfB+LzX8R3UQ30U10E91EN9FNdBPdRDfRTXQT3UQ30U10E91EN9FNdBPdRDfRTT+3dgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t, #"Year + Month" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", type text}, {"Year + Month", type text}}),
#"CC: Month #" = Table.AddColumn(#"Changed Type", "Month #", each Date.Month(Date.From("1-"&[Month]&"-"&Text.From([Year])))),
#"CC: Fiscal Year" = Table.AddColumn(#"CC: Month #", "Fiscal Year", each Number.RoundDown([#"Month #"]/7)+[Year]),
#"CC: Fiscal Month" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]<7) then [#"Month #"]+6 else [#"Month #"]-6),
#"Changed Type1" = Table.TransformColumnTypes(#"CC: Fiscal Month",{{"Fiscal Month", Int64.Type}, {"Month #", Int64.Type}, {"Fiscal Year", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Fiscal Year", Order.Ascending}, {"Fiscal Month", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Value", 500, 1)
in
#"Added Index"File:
Assuming you have [Year] (eg. ####) and [Month] (eg. "Aug") in your table you can use M to derive the Month # in the following way.
= Table.AddColumn(#"NameofyourPreviousStep", "Month #",
each Date.Month(Date.From("1-"&[Month]&"-"&Text.From([Year]))))
If youre interested in calculating Fiscal Year with an October start check out this:
= Table.AddColumn(#"NameofYourPreviousStep", "FY", each Number.RoundDown([#"Month #"]/10)+[Year])
If youre interested in calculating Fiscal Month with an October start check out this:
= Table.AddColumn(#"NameofYourPreviousStep", "Fiscal Month",
each if([#"Month #"]<10) then [#"Month #"]+3 else [#"Month #"]-9)