Average open time for reports open a given month

[Anonymous]

OK, this question might be a bit "Hairy", but here goes:

I have a Sharepoint List used as a Non-conformity database.

For each Non-conformity Report, I have a Created date 'NCR'[Created], a Closed Date 'NCR'[NCRFinalDate] and a 'NCR'[Now] column showing today's date.

Now for the hairy bit: I need to know the average "Age" (time from [Created] to End-of-Month) for all open NCRs ( 'NCR'[Created] < End-of-Month AND 'NCR'[NCRFinalDate] > End-of-Month) for each month in my data-set, which starts Sept-2017.

I use this Measure to get the number of open NCRs in a given month:

# of open NCR per Month =

VAR _Day =

EOMONTH ( MAX ( 'Month'[Month] ), 0 )

RETURN

COUNTROWS (

FILTER (

ALL ( 'NCR' ),

'NCR'[Created] <= _Day

&& 'NCR'[NCRFinalDate] > _Day

)

)

Can something similar be used for this task?

/Jens

[johnt75]

Yes, you can replace the COUNTROWS with AVERAGEX

Average duration =
VAR _Day =
    EOMONTH ( MAX ( 'Month'[Month] ), 0 )
RETURN
    AVERAGEX (
        FILTER ( ALL ( 'NCR' ), 'NCR'[Created] <= _Day && 'NCR'[NCRFinalDate] > _Day ),
        'NCR'[NCRFinalDate] - 'NCR'[Created]
    )

[Anonymous]

Hi John!

Thank you for your answer, your solution does gives the wrong result, however, as the "Age" of the NCRs in a given month is (End-of-month - 'NCR'[Created]), not ('NCR'[FinalDate] - 'NCR'[Created]).

Any way of getting an End-of-month in there?

/Jens

[johnt75 · Accepted solution]

In the last line replace 'NCR'[NCR Final Date] with _Day

[Anonymous]

Thanks!