A cyclic reference was encountered during evaluation

[ajm]

Hi all, i am trying to determine which projects from my list are active. the status of the project depends on the Start and End Dates compared to a range selected by the user in a slicer. I have set up a measure to capture these slicer dates.

StartDateFP = CALCULATE ( MIN ( ExtXLFileDates[Date] ), ALLSELECTED ( ExtXLFileDates[Date] ) )
EndDateFP = CALCULATE ( MAX ( ExtXLFileDates[Date] ), ALLSELECTED ( ExtXLFileDates[Date] ) )

ExtXLFileDates is a sperate list of dates, every day, spanning several years. I have used this format in the ExtXLFileDates table to set up these measures when i was experimenting and there were no issues, so don't think this will be where the problem is.

In my routine, i have the following section to compare the project Start and End dates to these measures. Below:

Result2 = Table.AddColumn(#"Expanded Table1", "ActiveProject", each let 
                activeperiodstartdate = ForwardPlan[StartDateFP],
                activeperiodenddate = ForwardPlan[EndDateFP]
               
            in 
                if ([Activity Start]="null" and [Activity End]="null") then "N" else
                if ([Activity Start]="null" and [Activity End]>activeperiodstartdate and [Activity End]<activeperiodenddate) then "Y" else
                if ([Activity End]="null" and [Activity Start]>activeperiodstartdate and [Activity Start]<activeperiodenddate) then "Y" else 
                if ([Activity Start]>activeperiodstartdate and [Activity Start]<activeperiodenddate) then "Y" else
                if ([Activity End]<activeperiodenddate and [Activity End]>activeperiodstartdate) then "Y" else
                if ([Activity End]<activeperiodstartdate and [Activity End]>activeperiodenddate) then "Y" else "N")

Somewhere in those lines is the cause of the cyclic reference.

Not every project line has date details. Some project lines have a Start Date and no End Date, some have an End Date but no Start Date, some have both dates.

I worked out the formula in excel before transferring it and adapting it to PBi but must have stuffed up somehow.

'/// For project lines with no dates, input "N"

if ([Activity Start]="null" and [Activity End]="null") then "N" else"

'/// for projects with no Start Date, but an End Date. That End Date has to greater than the "activeperiodstartdate" selected by the user on the slicer, and less than the "activeperiodenddate" selected by the user. (this needs refinement). Input "Y"

 if ([Activity Start]="null" and [Activity End]>activeperiodstartdate and [Activity End]<activeperiodenddate) then "Y" else

'/// for projects with no End Date, but a Start Date. That Start Date has to greater than the "activeperiodstartdate" selected by the user on the slicer, and less than the "activeperiodenddate" selected by the user. (this needs refinement). Input "Y"

if ([Activity End]="null" and [Activity Start]>activeperiodstartdate and [Activity Start]<activeperiodenddate) then "Y" else

'/// for projects with both dates, the activity has to start after the user's earlier selected date and before the users end range date. Input "Y"

if ([Activity Start]>activeperiodstartdate and [Activity Start]<activeperiodenddate) then "Y" else

'///AND the Activity End Date must be before the later of the dates selected by the user and after the earlier date in the range selected.

 if ([Activity End]<activeperiodenddate and [Activity End]>activeperiodstartdate) then "Y" else

'/// Finally, include the project if its StartDate is before the lower selected date and after the higher selected date

 if ([Activity End]<activeperiodstartdate and [Activity End]>activeperiodenddate) then "Y" else "N")

hopefully if you have read this far, you have a solution or some pointers on how to find the solution. I think it has to do with the way i am trying to cover all date scenarios. any help would be greatly appreciated.

 

[ajm]

for whatever reason, the way i did the measures was causing the problem. don't know why, just was.