Dax issue with Circular Referencing

Hello Team,

I’m attempting to formulate a measure as outlined below, but I’ve encountered a challenge with circular referencing. The issue appears straightforward, yet I find myself unable to resolve it. I kindly seek the assistance of our community members for their valuable guidance.

Link for File - https://docs.google.com/spreadsheets/d/1BEjr1GnyGyr6MqkD5LKlgm13Pvb2efz_/edit?usp=sharing&ouid=105025858095254050418&rtpof=true&sd=true

The Excel File contains the following -

Sheet Named ‘Input Table’ - containing Table Named ‘tInput’ which has been added in power query and connection has been made.
Sheet named ‘Report’ - which contains power pivot of previous table.

Given Data - It is a report with head count calculation. The table has opening head count, recruitment, Attrition % (which is applied on opening head count).

Required - Dax Measure which shall calculate ‘Closing Head count’ & Opening Head for every month, and attrition count which is Attrition % multiplied by Opening Head Count.

Issue - While creating DAX Measure for attrition % or Opening Head i am experiencing Circular referencing issue and hence unable to solve.

Request for help.

DAX Issue.xlsx (275.2 KB)


Did you try to use variables instead of measures for head count calculations to avoid circular dependencies?

Hi @otets1 ,

I am not sure where i am going wrong. I have tried Variables. Below are the measures i tried with error message.

Error -

Attrition Count Measure -

Closing Head Count Measure -

Please help.

Hi @otets1 ,

Can you please help me figure out a solution.

Thanks in advance.

Hello. I tried to calculate the metrics you need, but unfortunately, I couldn’t manage it. This is a classic circular dependency between the measures, not a context transition issue for the calculated columns, so I’m not smart enough to solve it. Maybe someone more experienced will have a solution.

See if it helps.
I did it in Power BI, but try doing the same in Excel
Percentual2.pbix (23.2 KB)