Forum Discussion

francofava's avatar
francofava
New Member
3 years ago
Solved

Reverse sort in date table

I have created a date table using the code below.  Accounting_Posting_Date = ADDCOLUMNS ( CALENDAR (DATE(2015,1,1), DATE(2030,12,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "...
  • francofava's avatar
    francofava
    3 years ago

    I've managed to solve it by adding the column directly when creating the table and then referring to it when sorting the other columns

    Accounting_Posting_Date =
    ADDCOLUMNS (
    CALENDAR (DATE(2015,1,1), DATE(2030,12,31)),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Period", FORMAT ( [Date], "YYYYMM" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "Period_rank", -value(FORMAT ( [Date], "YYYYMM" ))
    )
Uspace87's avatar
Uspace87
Resolver III
3 years ago

francofava 

 

you should create an index and then sort you "Period" column by the index. 

 

Try to sort it by "Month Number" and see what happens.

francofava's avatar
francofava
New Member
3 years ago

thanks Uspace87 

Can I do this in DAX?

the few examples that I've tried also gave me the circular dependency error

  • Uspace87's avatar
    Uspace87
    Resolver III
    3 years ago

    francofava 

     

    you don't need to do it in dax.

     

    In you calendar table just sort you "Period" column by "Month_Year". The first time it will do it descending and the ascending or the other way around.