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

anupampandey's avatar
anupampandey
Helper III
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