Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
the DATESBETWEEN was in the wrong place
Average Connector Usage =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
VAR minDate =
MIN ( 'FACT TABLE'[Start Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Start Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
),
DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
)
)
You could try
Average COALESCE =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
VAR minDate =
MIN ( 'FACT TABLE'[Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
)
),
DATESBETWEEN ( 'Date'[Date], minDate, maxDate )
)
( 'Date'[Date], minDate, maxDate )
I've only got available [Date] options:
'FACT TABLE'[Date]
or
'Calendar'[Date]
minDate and maxDate are the variables created earlier in the code. You want to find the earliest and latest dates from your fact table, not your calendar table - your calendar table will likely have dates before and after you have any facts
Average Connector Usage =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
VAR minDate =
MIN ( 'FACT TABLE'[Start Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Start Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
)
),
DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
)
Thank you johnt75,
That makes sense.
I've edited my formula, but I am receiving an error: The syntax for ',' is incorrect;
Please check my formula and see what I have typed incorrectly? Thank you so much.
You only put the VAR keyword the first time you are declaring the variable, you don't use it when using the variable later in the formula
Average Connector Usage =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
VAR minDate =
MIN ( 'FACT TABLE'[Start Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Start Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
)
),
DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
)
I've copied your formula and I get error message:
'Failed to resolve name 'minDate'. It is not valid table, variable or function name.
the DATESBETWEEN was in the wrong place
Average Connector Usage =
IF (
NOT ISEMPTY ( 'FACT TABLE' ),
VAR minDate =
MIN ( 'FACT TABLE'[Start Date] )
VAR maxDate =
MAX ( 'FACT TABLE'[Start Date] )
RETURN
CALCULATE (
AVERAGEX (
VALUES ( 'Calendar'[Date] ),
COALESCE ( [SDR ID average per CP ID], 0 )
),
DATESBETWEEN ( 'Calendar'[Date], minDate, maxDate )
)
)
Oh my gosh johnt75; It actually works!!!! 😄
I am ever so grateful. Thank you so so so very much!!!!!!! ❤️ 😉