A cyclic reference was encountered during evaluation

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,056
Office Version
  1. 365
Platform
  1. Windows
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:

Power Query:
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"
Power Query:
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"
Power Query:
 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"
Power Query:
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"
Power Query:
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.
Power Query:
 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
Power Query:
 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.
 
Last edited by a moderator:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
for whatever reason, the way i did the measures was causing the problem. don't know why, just was.
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,121
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top