Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
francofava
New Member

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" ),

"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" )

)
 
I am using the PERIOD in a matrix column, but cannot manage to reverse the periods.
francofava_0-1670505571853.png
 
I've tried creating a new column
 "RANK_" = -Accounting_Posting_date[Period] 
and then use it to sort the period column but I get an error message about a circular dependency.
francofava_1-1670505774279.png

thanks!

1 ACCEPTED SOLUTION
francofava
New Member

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" ))
)

View solution in original post

4 REPLIES 4
francofava
New Member

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
Resolver III
Resolver III

@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.

thanks @Uspace87 

Can I do this in DAX?

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

@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.

 

Uspace87_0-1670507822690.pngUspace87_1-1670507842094.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors