Circular Dependency Error for Calculate

Charuta — Wed, 27 Jul 2022 13:19:06 GMT

Hello All,

Need help to fix circular dependency in order to get the final date

In calculations, need to average and then find how much time is required for completion based on last 2 months average.

Columns - Status ( there are 2 status - completed and pending) ,project, points, closingdate, work_left

[points] are at [mainkey_id] level and rolled up to project level

work_left is sum of points at [mainkey_id] level with [status] as pending

DateDim is calculatedtable having all dates and joined with closingdate from Query1

based on completed status for last 2 months we add the points per project and take an average.

[AvgWorkDone] =

IF(ISBLANK(CALCULATE(SUM('Query1'[points]),DATESBETWEEN(DateDim[Date],TODAY()-60, TODAY()) , ALLEXCEPT('Query1','Query1'[mainkey_id]))/2 ),0,CALCULATE(SUM('Query1'[points]),DATESBETWEEN(DateDim[Date],TODAY()-60, TODAY()) , ALLEXCEPT('Query1','Query1'[mainkey_id]))/2 )

The report has to show all data so we have data pulled for last 2 years and for computing final date we need to consider only last 2 months average.

we get the correct average and even if we take the ratio of this average based on the work pending we get the right result in a measure. Calculated Column does not show the right result.

This still works fine, but the calculated number of days are to be added in today's date so using DateAdd get circular dependency. Tried using ALLEXCEPT but nothing works..

to calculate the final date ---

CALCULATE
(

DATEADD(
Query1[DateToday],
DIVIDE(
SUM(Query1[work_left]),
CALCULATE(SUM(Query1[AvgWorkDone]),ALLEXCEPT(Query1,Query1[mainkey_id]))
,0)
,DAY)
,ALLEXCEPT(Query1,Query1[[mainkey_id])

not sure what needs to be changed or calculated differently. Any help much appreciated

Re: Circular Dependency Error for Calculate

lbendlin — Fri, 29 Jul 2022 00:31:42 GMT

If possible, push the calculated columns computation further upstream (into Power Query or the data source). That will avoid the circular reference issue.

Same for the DateDim - don't do that in DAX, do it in Power Query or further upstream.

topic Re: Circular Dependency Error for Calculate in DAX Commands and Tips

Circular Dependency Error for Calculate

Re: Circular Dependency Error for Calculate