Forum Discussion

shaunwilks's avatar
shaunwilks
Icon for Helper V rankHelper V
9 years ago
Solved

Month sort difficulties when excluding year

Best I illustrate with data.     I have 3 years of Financial Data. The Fiscal year is 01 July - 30 June. Values for each year are posted with a period number from that year 1-12. ie July = Perio...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    no you don't need to create an extra table.

     

    Somehow there is a little glitch in your PeriodNbr column, the number 12 is assigned to May in the year 2016 and June in the year 2017. Have a closer look at the datetime may 2016, it's duplicate.

     

    If you fix this the error will disappear.

     

    Regards

AlbertoFerrari's avatar
AlbertoFerrari
Icon for Most Valuable Professional rankMost Valuable Professional
9 years ago

If you want July to be first and June to be the last, then you need to build a column in your date table that stores 1 for July, 2, for August and so on until you assign 12 to June, the code is pretty simple.

For each month name there need to be only one value in that column. If you add the year, then January will have different values for different years.

I wrote extensively about this in my last modeling book and you can find more information (although I don't remember if we treat sorting of fiscal months) in the Time patterns at DAX Patterns.


Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com