Numer of open reports per month based on Created Date and Closed Date

[Anonymous]

Hi all

I have a Sharepoint List which is used for registering and hanling NCRs (Non-Conformity Reports).

I would like to track the number of open NCRs per month historically based on [Created Date] and [Closed Date] on the individual NCRs.

Also I would like suggestions as to how to handle Open NCRs ([Closed Date]=null

Thanks in advance!

/Jens

[Tahreem24]

@Anonymous  Could you share the sample dummy data?

[Anonymous]

NCR_Number	Created	NCRFinalDate
301	27-09-2017 15:08	23-04-2018
302	27-09-2017 15:55	25-11-2019
303	28-09-2017 09:53	02-02-2018
304	28-09-2017 10:04	02-02-2018
305	02-10-2017 09:34	15-11-2017
306	02-10-2017 09:44	15-11-2017
307	04-10-2017 14:04	30-11-2017
308	05-10-2017 12:27	10-10-2017

[Tahreem24]

@Anonymous ,First create a Date only column from Created column.

Created Date Column =FORMAT(NCRTable[Created],"DD/MM/YYYY")

Then, create a measure like below:

Open NCR = CALCULATE(COUNTROWS(NCRTable),ALLEXCEPT(NCRTable,NCRTable[Created Date Column]))

[Anonymous]

Maybe I didn't pose my issue clearly enough ....

What I would like is to see the number of open NCRs month by month, historically, based on the created and NCRFinalDate. So with the dummy data above you'd get the data in the # of open NCR column below

Month	# of open NCR	Comment
sep-17	3	(number 301, 302, 303 and 304 all created sep-17, but not closed by sep-17)
oct-17	7	(the ones above + 305, 306 and 307 all of which were created in or before oct-17, but not closed by the end of oct-17. 308 is not counted because it was closed oct-17)
nov-17	4	(305, 306 and 307 closed during nov-17)
dec-17	4	(no change)
jan-18	4	(no change)
feb-18	2	(303 and 304 closed)
mar-18	2	(no change)
apr-18	1	(301 closed)
maj-18	1	(no change)

so something like

OpenNCRsep-17 = IF NCR[created]<=sep-17 AND NCR[NCRFinalDate]>=sep-17 

it should count as being open in sep-17, and so an and so forth giving me the number of open NCR per month since sep-17 until last month end

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I create a sample, there are two tables.

Here's my solution. 

Create a measure.

# of open NCR =
VAR _Day =
    EOMONTH ( MAX ( 'Month'[Month] ), 0 )
RETURN
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Created] <= _Day
                && 'Table'[NCRFinalDate] > _Day
        )
    )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks! Worked a charm!