Fiscal Year and Month Order

[Pammy2411]

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

[ankitpatira]

@Pammy2411 'FY15 August' and 'FY16 August' have different names so if column is of type Text it should not group.

[Pammy2411]

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 · Accepted solution]

@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

[medhatmorsi]

@MarkDGaal Thank you for your sample code, but in my case the fiscal year starts at "April" and I can't mange to reuse your code, appreciate your support. 

[MarkDGaal]

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)