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
