Sort Column Chart (Circular Dependency Error)

[third_hicana]

Hi All,

Asking for your help. I have a date column in my table named Project Completion Date. I created a calculated column to extract the year referencing the  Project Completion Date column using this DAX.

Project_Completion_Date(YYYY) =

FORMAT(FY25_PB_Master_Data[Project Completion Date],"yyyy")

I wanted to sort my column chart by calculated year. I tried sorting it using another calculated column with the Dax expression below. However, it says that there is a circular dependency error that's why it was not sorted. Appreciate your help. Thank you.

CompletionYearOrder =

IF(FY25_PB_Master_Data[Project_Completion_Date(YYYY)] = "2023", 1,

IF(FY25_PB_Master_Data[Project_Completion_Date(YYYY)] = "2024", 2,

IF(FY25_PB_Master_Data[Project_Completion_Date(YYYY)] = "2025", 3,

IF(FY25_PB_Master_Data[Project_Completion_Date(YYYY)] = "2026", 4,

IF(FY25_PB_Master_Data[Project_Completion_Date(YYYY)] = "2027", 5,

IF(FY25_PB_Master_Data[Project_Completion_Date(YYYY)] = "2028", 6

))))))

[Anonymous · Accepted solution]

Hi @third_hicana 

You just need to use year() function to extract the year, it will return the value with number type, then put the column to the x-axis, it will order automatically.

Project_Completion_Date(YYYY) =
YEAR ( FY25_PB_Master_Data[Project Completion Date] )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Idrissshatila]

Hello @third_hicana ,

create this as a custom column in power query 

Project_Completion_Date(YYYY) =

FORMAT(FY25_PB_Master_Data[Project Completion Date],"yyyy")

and then the second column you're creating should work normlly

[third_hicana]

Hi @Idrissshatila 

I got this error when I created another column in the Custom Column in Power Query

[Idrissshatila]

@third_hicana ,

yes , you can't copy paste the dax measure in power query since they're two different measures.

so check it here to know how to extract the year from the date column.