Average auto-Calculation

The main reason for the difference is that your column for days open returns blank if the complaint is closed on the same day it's opened.  If you remove the = from <= in that expression, the results get much closer.  The small difference after that is because you have one complaint with a closed date < open date.  Since that one included in the complaint count but not the days open total, the result is incorrect.  I believe the correct result is 147.06.  

 

A couple things:

 

You don't need the CALCULATE around your _TotalDaysOpen and _TotalComplaints measures.

 

I would rewrite your column like this (to be more efficient).

 

Days Open Column =
VAR vCloseDate =
    IF (
        ISBLANK ( COMPLAINTS[Closed_Date] ),
        TODAY (),
        COMPLAINTS[Closed_Date]
    )
VAR vDaysOpen =
    DATEDIFF (
        COMPLAINTS[Opened_Date],
        vCloseDate,
        DAY
    )
RETURN
    IF (
        vDaysOpen >= 0,
        vDaysOpen,
        BLANK ()
    )

 

 

You also don't need the column and can do it all in a measure.  However, if you plan to use that column in a visual axis or legend, you need it.

 

New Avg Open =
VAR vSummary =
    ADDCOLUMNS (
        SUMMARIZE (
            COMPLAINTS,
            COMPLAINTS[ID],
            COMPLAINTS[Opened_Date],
            COMPLAINTS[Closed_Date]
        ),
        "cDaysOpen",
            DATEDIFF (
                COMPLAINTS[Opened_Date],
                IF (
                    ISBLANK ( COMPLAINTS[Closed_Date] ),
                    TODAY (),
                    COMPLAINTS[Closed_Date]
                ),
                DAY
            )
    )
RETURN
    AVERAGEX (
        FILTER (
            vSummary,
            [cDaysOpen] >= 0
        ),
        [cDaysOpen]
    )

 

 

Regards,

Pat