Coalesce excluding pre and post data

[Anonymous]

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 )

    )

)

[johnt75]

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

[Anonymous]

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]

or

'Calendar'[Date]

[johnt75]

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

[Anonymous]

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 )

)

[johnt75]

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

[Anonymous]

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

[johnt75 · Accepted 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 )
        )
)

[Anonymous]

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