Average no. of days between two days

[Anonymous]

There are 2 visual as below:

I created the below measure to calculate the average number of days taken from start date to end date per employee, where end date is not blank.

Avg Days to Close =

CALCULATE(

AVERAGEX(DATA,DATA[End Date]-DATA[Start Date)

,FILTER(

               ALLEXCEPT(DATA,DATA[Employee Name],DATA[Start Date])

                   ,NOT(ISBLANK(DATA[End Date]))

            )

       )

Sample Data used to create the above visuals:

The above measure is working fine in the table visual. But when i select any particular month in the bar graph, the result of average days to close a request is not changing and getting same value for all the month. See the below attached screenshot when i filtered with a selected a particular month.

I have included the start data in the ALLEXCEPT() formula, yet it is not working. What error did I commit? Please help me in resolving this issue.

 

In the below screeshot, i selected Jan, yet the avg days to close column not getting changed @Fowmy 

[mahoneypat]

The ALLEXCEPT in your original measure was removing the filter from the Month column, as you did not specify it.  It kept the filter on start date, which is not used in your visual.  For this measure, as the Filter on employee name comes from the visual, the only change to the evaluation context needed is the FILTER to avoid blank end dates.

Regards,

Pat

[mahoneypat · Accepted solution]

Your ALLEXCEPT is removing the filter on the Month column which is why it is not changing.  A simpler way to write your measure might be

Avg Days to Close =
AVERAGEX (
    FILTER (
        DATA,
        NOT (
            ISBLANK ( Data[End Date] )
        )
    ),
    DATEDIFF(DATA[Start Date], DATA[End Date], DAY)
)

Regards,

Pat

[Anonymous]

@mahoneypat Thanks for the solution, this worked. I have a query on this, the ALLEXCEPT() function is filtering on employee name in the table visual, as it is filtering data for the employee name and why is it not filtering with the start month

[Greg_Deckler]

@Anonymous - Where is your Month column coming from? Is it related to your Start Date?

[Anonymous]

@Greg_Deckler Yes month column is coming from Start Date