Forum Discussion

Pammy2411's avatar
Pammy2411
Frequent Visitor
9 years ago
Solved

Fiscal Year and Month Order

Hi,   I currently have a slicer with Fiscal Year and a line graph with revenue/month.   However, whenever i select more than one Fiscal Year, the months group themselves together. As in, FY15 Aug...
  • MarkDGaal's avatar
    MarkDGaal
    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. 

    1. The 1st column calculates the Month Number using the 3-letter Month abbreviation found in the [Month] column.
    2. The 2nd column calculates the Fiscal Year based on a July start. (eg. 2014 June is FY 2014, but 2014 July is FY 2015)
    3. The 3rd column calculates the Fiscal Month # based on a July start.
    4. New columns types are changed to Whole Numbers
    5. The table is then sorted ascending by Fiscal Year and then Fiscal Month giving you the order you are looking for
    6. 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:

    PammyFiscalMonthSort.pbix