Forum Discussion

JustShaun's avatar
JustShaun
Regular Visitor
9 years ago
Solved

Sort dates by financial month order

When i use my date data in a report i want my months to be in the order of financial month (Apr=1, May=2......). My solution was to create a Calculated column in the same table as my dates as below. ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi JustShaun,

     

    Based on my research, a workaround is recreating the FinancialMonthNumber in the Query Editor. If you go to Edit Queries, you can use the Add Custom Column feature in the Add Column tab in the ribbon. Use this formula:

    if Date.Month([FullDateAlternateKey])<4 then Date.Month([FullDateAlternateKey])+9 else Date.Month([FullDateAlternateKey])-3

    Once you Close and Apply, you can then go to the Data pane and sort your dates column by FinancialMonthNumber. Here is the similar thread for your reference.:smileyhappy:

     

    Regards

  • anupampandey's avatar
    anupampandey
    9 years ago

    Hi JustShaun

     

    You need to create a financial month column in date table / calendar. I have used below calculation for the same

     

    FiscalMonth = SWITCH(MONTH(Calendar[DateKey].[Date]),1,10,2,11,3,12,4,01,5,02,6,03,7,04,8,05,9,06,10,07,11,08,12,09)

     

    I hope this way can sort your data by financial month.

     

    All the best!

     

    Anupam

Kinsey's avatar
Kinsey
Advocate III
9 years ago

A great way to place a dynamic table in any report in future which creates a British finanical year (April to March) and filterable by all fields with the correct ordering is to create this calculated table (just select 'new table' in the normal view. Once created You would just need to change the date column to a date and 'sort by' the month by month number and day by day number and then create the relationships you wish. It does everything you need:

 

DateTable = ADDCOLUMNS (
    CALENDAR (MIN(insert your table date column here),MAX(insert your table date column here)),
    "Day",FORMAT([Date],"DDDD"),
    "DayNum",WEEKDAY([Date]),
    "Year", FORMAT([Date],"YYYY"),
    "Month", FORMAT([Date],"MMMM"),
    "MonthNum",SWITCH(MONTH([Date]),
                1, "10",
                2, "11",
                3, "12",
                4, "01",
                5, "02",
                6, "03",
                7, "04",
                8, "05",
                9, "06",
                10, "07",
                11, "08",
                "09" ),
    "Qtr", SWITCH( MONTH([Date]),
                1, "Qtr4",
                2, "Qtr4",
                3, "Qtr4",
                4, "Qtr1",
                5, "Qtr1",
                6, "Qtr1",
                7, "Qtr2",
                8, "Qtr2",
                9, "Qtr2",
                10, "Qtr3",
                11, "Qtr3",
                "Qtr3" ),
    "MthYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),
    "QtrYr",CONCATENATE(SWITCH( MONTH([Date]),
                1, "Qtr4",
                2, "Qtr4",
                3, "Qtr4",
                4, "Qtr1",
                5, "Qtr1",
                6, "Qtr1",
                7, "Qtr2",
                8, "Qtr2",
                9, "Qtr2",
                10, "Qtr3",
                11, "Qtr3",
                "Qtr3" ),FORMAT([Date]," YYYY")),
    "FinYr", SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1),
    "QtrFinYr", CONCATENATE( SWITCH( MONTH([Date]),
                1, "Qtr4",
                2, "Qtr4",
                3, "Qtr4",
                4, "Qtr1",
                5, "Qtr1",
                6, "Qtr1",
                7, "Qtr2",
                8, "Qtr2",
                9, "Qtr2",
                10, "Qtr3",
                11, "Qtr3",
                "Qtr3" )," "&SWITCH(TRUE(),Month([Date])<4,YEAR([Date])-1 &"-"&YEAR([Date]),YEAR([Date])&"-"&YEAR([Date])+1))
    )