Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Cyclical errors occur when a value in a calculation refers to itself, like:
NewTable = Table.Split(NewTable, 2)
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
thank you very much i believe you saved my job
Now I run tests with the other months as well
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.