Error cyclic dependency when sorting by calculated column.

[zidek22]

Hello data wizards,

I have this problem in Power BI desktop. I need to sort months in a matrix according to their year. The months go like this:

Jan, Feb, Mar, Apr, May, Dec

the December here is from the previous year, so I want:

Dec, Jan, Feb, Mar, Apr, May

I have created a calculated column that goes like columnForSort = FORMAT(tablename[DateCol]; "YYYYMM") which I want to use for sorting, because there the dates look like this: 201912, 202001, 20202, etc.

Perfect, but now when I click on the Date column in the fields tab and then I "sort by column" and choose the new columnForSort it loads for a bit and then an error pops up that reads: 

Failed to save modifications to a server. Error: Cyclic dependency detected: tablename[Date], tablename[columnForSort], tablename[Date].

I guess the problem is with sorting an original column by a new column that was created in accordance to the original column.

Is there a way to work around this?

THANK YOU

[amitchandak]

@zidek22 , I am not sure How you have used it.

But You can not sort a derived column on the base column

This how we create a column and its sort column

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")

Month= FORMAT([Date],"mmm")
Month sort = FORMAT([Date],"mm")

Both coming from same column, Not A-> b-> C

[zidek22]

Thank you for your reply @amitchandak !

I have a base column and I made one derived column. Then I highlight the base column and click "sort by column" and choose the derived column. I've already done this in a different project where I needed to sort months chronologicaly and not alphabeticaly and it worked great. I'm probably just doing some small mistake. Anyway, thank you for your time!

[amitchandak]

@zidek22 , I have given formulas in last update to sort the month on month order, not the text. Just adjust to your table and that will work

[zidek22]

It worked! I added a second DateView column that I use to display the dates, then I created the DateSort column that is in a YYYYMM format and can be sorted. After that I sorted the DateView column by the DateSort column. Basicaly I never use the original Date column in my visual. Just writing this up for future reference if anyone needs it.

Thank you so much! Bye @amitchandak