Forum Discussion
If statement with Circular Dependency
Anonymous
I think I may be able to accomplish it all in the editor with the DateTime.Date( DateTime.LocalNow() ). I'm going to give it a shot. In the mean time here whole formula. Its just the same type of logic as the portion I supplied repeated over and over.
Days in Status (Base Work) =
IF (
'NOT-WORKED'[- Status Date] <= 0 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
)
&& 'NOT-WORKED'[- Status Date] >= -10 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Rehab Status] )
),
"Upcoming",
IF (
'NOT-WORKED'[- Status Date] > 0 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Rehab Status] )
)
&& 'NOT-WORKED'[- Status Date] <= 15 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
),
"0 to 15 days",
IF (
'NOT-WORKED'[- Status Date] > 15 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
)
&& 'NOT-WORKED'[- Status Date] <= 30 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
),
"16 to 30 days",
IF (
'NOT-WORKED'[- Status Date] > 30 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
)
&& 'NOT-WORKED'[- Status Date] <= 45 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
),
"31 to 45 days",
IF (
'NOT-WORKED'[- Status Date] > 45 + CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Status] )
),
"46+ days",
"x"
)
)
)
)
)
and the relationships
use variables...this will run MUCH faster
In this case you're only calculating the Status Average and Rehab Average ONCE, and then reusing the same value each time. You also only need to maintain 1 copy of the code, and any changes to make to it are automatically applied to all instances of that variable.
Use SWITCH() instead of nested IF() statements, much easier to read. SWITCH( TRUE(), ...) is even more flexible, and lots of documentation exists on this pattern.
Days in Status (Base Work) =
VAR Status_Average =
CALCULATE ( AVERAGE ( 'NOT-WORKED'[Base Range] ), ALL ( Vlookup[Status] ) )
VAR Rehab_Average =
CALCULATE (
AVERAGE ( 'NOT-WORKED'[Base Range] ),
ALL ( Vlookup[Rehab Status] )
)
RETURN
SWITCH (
TRUE (),
'NOT-WORKED'[- Status Date]
<= 0 + Status_Average
&& 'NOT-WORKED'[- Status Date]
>= -10 + Rehab_Average, "Upcoming",
'NOT-WORKED'[- Status Date]
> 0 + Status_Average
&& 'NOT-WORKED'[- Status Date]
<= 15 + Rehab_Average, "0 to 15 days",
'NOT-WORKED'[- Status Date]
> 15 + Status_Average
&& 'NOT-WORKED'[- Status Date]
<= 30 + Rehab_Average, "16 to 30 days",
'NOT-WORKED'[- Status Date]
> 30 + Status_Average
&& 'NOT-WORKED'[- Status Date]
<= 45 + Rehab_Average, "31 to 45 days",
'NOT-WORKED'[- Status Date]
> 45 + Status_Average, "46+ days",
"x"
)