topic Re: Coalesce excluding pre and post data in DAX Commands and Tips

Coalesce excluding pre and post data

Anonymous — Wed, 17 Aug 2022 07:52:51 GMT

Hi,
This formula works perfect if is inscope, meaning if I select the start date and last date of the analysed item;
Without Date filters the formula calculates ALL zero's from the 1st day of my calendar date till last: December 2022. With this my averages are way off. How can this formula be improved please? Thank you

Average COALESCE =

IF (

NOT ISEMPTY ( 'FACT TABLE' ),

AVERAGEX (

VALUES ( 'Calendar'[Date] ),

COALESCE ( [SDR ID average per CP ID], 0 )

)

Re: Coalesce excluding pre and post data

johnt75 — Wed, 17 Aug 2022 08:21:42 GMT

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 ) )

Re: Coalesce excluding pre and post data

Anonymous — Wed, 17 Aug 2022 08:39:38 GMT

Hi johnt75,

Thank you so much. I've tried your formula, editing the FACT TABLE

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], MIN('Calendar'[Date], MAX('Calendar'[Date])

)

I receive a warning : too few arguments for the DATESBETWEEN function.
I guess I got lost on DATESBETWEEN because I'm not sure what these are:

( 'Date'[Date], minDate, maxDate )

I've only got available [Date] options:

'FACT TABLE'[Date]

'Calendar'[Date]

Re: Coalesce excluding pre and post data

johnt75 — Wed, 17 Aug 2022 08:47:08 GMT

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 ) )

Re: Coalesce excluding pre and post data

Anonymous — Wed, 17 Aug 2022 08:57:00 GMT

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.

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], VAR minDate, VAR maxDate )

)

Re: Coalesce excluding pre and post data

johnt75 — Wed, 17 Aug 2022 09:02:30 GMT

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

Re: Coalesce excluding pre and post data

Anonymous — Wed, 17 Aug 2022 09:07:21 GMT

I've copied your formula and I get error message:
'Failed to resolve name 'minDate'. It is not valid table, variable or function name.

Re: Coalesce excluding pre and post data

johnt75 — Wed, 17 Aug 2022 09:14:32 GMT

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 ) ) )

Re: Coalesce excluding pre and post data

Anonymous — Wed, 17 Aug 2022 09:24:49 GMT

Oh my gosh johnt75; It actually works!!!! 😄
I am ever so grateful. Thank you so so so very much!!!!!!! ❤️ 😉