Calculate Average with filters and If statements

[kweibelt]

I am a bit out of practice and have recently been using PBI again. I have a column that calculates if we are before or past the due date in days:

Schedule Adhereance =

IF(ISBLANK(Request[Completed]), 0,

if(

    AND(

        ISBLANK(Request[Due]), ISBLANK(Request[OriginalDue])),

        -1*(Request[TargetComplete] - Request[Completed]),

        if(

        ISBLANK(Request[Due]),

        -1*(Request[OriginalDue] - Request[Completed]),

            -1*(Request[Due] - Request[Completed])))

            )

I need for it to also filter based on the Status UAT or Done. I have tried many variations but am unable to get this to work.

For each record I expect to see the number of days but if I am using it in the dashboard I expect to see the average number of days.

[kweibelt]

@tamerj1 Thank you SO much! This is perfect. I completly forgot about Averagex and that was causing issues for me. 

[tamerj1 · Accepted solution]

Ji @kweibelt 

Please try creating a measure as follows 

Average Schedule Adhereance =
AVERAGEX (
Request,
IF (
ISBLANK ( Request[Completed] )
&& Request[Statis] IN { "UAT", "Done" },
0,
IF (
AND ( ISBLANK ( Request[Due] ), ISBLANK ( Request[OriginalDue] ) ),
( Request[Completed] - Request[TargetComplete] ),
IF (
ISBLANK ( Request[Due] ),
( Request[Completed] - Request[OriginalDue] ),
( Request[Completed] - Request[Due] )
)
)
)
)

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523