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 August and FY16 August are grouped together under August as one. This is understandable since they have the same month names.

However, when i select both years i want to see the effect over time and don't want them to group themselves.

As a solution i added FY behind every Month.

But this created Month ordering column to become very time consuming. I have to number every month of the year instead of just number 1-12. Is there a more efficient way of doing this?

Thanks,

Pamela

Need Help

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.

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:

PammyFiscalMonthSort.pbix

5 Replies

MarkDGaal

Helper III

9 years ago

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)

ankitpatira
Community Champion
9 years ago
Pammy2411 'FY15 August' and 'FY16 August' have different names so if column is of type Text it should not group.
- Pammy2411
  Frequent Visitor
  9 years ago
  Hi,
  Thanks for the answer. I am aware that they don't group together.
  I don't think I made my issue very clear. In order for data to be displayed in the correct order, being that the fiscal year starts from July and ends at June, I created a column displaying 1-12 in order to use sort by column in my 'months' column.
  
  However, when putting month+fy I have to number each month in all fy. Depending on how many fy there are in the database this can become time consuming. Is there a more efficient way of doing it?
  - MarkDGaal
    Helper III
    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:
    PammyFiscalMonthSort.pbix

Forum Discussion

Fiscal Year and Month Order

5 Replies

Related content

sorting months in order

Omitting months with Firm Orders

Orders Chart Independent from Fiscal Year Slicer

Fiscal Month Name

Create a date table that can show Fiscal Year and Fiscal Months in order