Dax issue with Circular Referencing

DAX DAX Calculations
1

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.

Regards,
Vikash
DAX Issue.xlsx (275.2 KB)

2

Hi,

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

3

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 -

image
image801×168 6.23 KB

Attrition Count Measure -

image
image660×555 14.7 KB

Closing Head Count Measure -

image
image666×552 13.3 KB

Please help.

4

Hi @otets1 ,

Can you please help me figure out a solution.

Thanks in advance.

5

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.

6

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