Cyclic reference during evaluation

[Anonymous]

Good Morning, 

I have this query but power query  said me that I have an error expression: Cyclic reference during evaluation

Table.AddColumn(#"Aggiunta colonna personalizzata3", "MeseGennaio", each if [DWH.GENNAIO] is null then (if Date.Month([Inizio Lavori])=1 then Merge1[Ricavo pesato]/(Date.Month([Fine Lavori]) + 1 - Date.Month([Inizio Lavori])) else null) else [DWH.GENNAIO])

Can someone help me?

Thank you everyone

[watkinnc]

Cyclical errors occur when a value in a calculation refers to itself, like:

NewTable = Table.Split(NewTable, 2)

[watkinnc · Accepted solution]

Actually, I'd write this as:

Table.AddColumn(#"Aggiunta colonna personalizzata3", "MeseGennaio", each if [DWH.GENNAIO] <> null then [DWH.GENNAIO] else if Date.Month(Merged1[Inzio Levori]) <> 1 then null else each Merge1[Ricavo pesato]/((Date.Month([Fine Lavori]) + 1)- Date.Month([Inizio Lavori])))

--Nate

[Anonymous]

thank you very much i believe you saved my job

Now I run tests with the other months as well

[watkinnc]

When you break this down logically, your query says:

Add column named MeseGennaio and then populate it like this:

if [DWH.GENNAIO] = null then do the following: if the month of [Inzio Lavori] is 1 then Merge1[Ricavo pesado]/Month number of [Fine Levori] + 1 -1. //-1 here is your - Date.Month([Inzio Lavori]) value and since you already said (if that value = 1 then do the rest of the calculation) then that value will always be -1.

I would suggest adding parentheses to make sure that your calculations are what you expect. It's difficult to understand if you are trying to divide Ricavo pesado by the month number +1, or by month number +1-1, or by month number, and then adding the integers. Remember, clearly separated in, then, else or elseifs, and clearly delineated parentheses for your calculations are important in if then else. 

--Nate

[Anonymous]

Hi, thanks for the answer.
The calculations are correct, in fact if I run the formula in January it works but not in February. It tells me it's cyclical. I should be running the formula for several months.
I don't understand what cyclical means