Count of incidents older than 3 years

[Anonymous]

I am counting incidents which were open and 3 years and over 3 years old.

I have a measure like this: 

CALCULATE(

                     COUNT(Incidents[IncidentID]),

                     FILTER( Incidents,

                     Incidents[Status] = "Open"

                     && TODAY() - Incidents[DateOpened] >= 365 * 3 )

                   )

I would like to see the table like this:

i.e,: In April 2022 there was one incident which was created on or before April 2019 in "Open" status. 

Any way to edit the above measure to include this?

Thanks!

[ryan_mayu]

could you pls provide the sample data?

[Anonymous]

I have attached my Gibhub link with PBI Incidents.pbix file.

https://github.com/Adityadambal/PowerBI/blob/6dd0b5b06fc0bfaee0ae529740c3f7caad155138/PBI%20Incident...

[ryan_mayu]

@Anonymous 

pls see the attachment below

[Anonymous]

Thanks for the reply. However, the output is not correct.

The status would be irrelevent for some old incidents in past years as the current status shows closed. I think using Closed Date and Incident date, we might be able to find the count.

[ryan_mayu]

@Anonymous 

you can try this

Measure = CALCULATE(COUNTROWS('QNCIncidents'),FILTER(all(QNCIncidents),'QNCIncidents'[Status (groups)]="Open" && DATEDIFF('QNCIncidents'[Incident Date],min('Dates'[Date]),MONTH)>=36))+ CALCULATE(COUNTROWS('QNCIncidents'),FILTER(all(QNCIncidents),'QNCIncidents'[Status (groups)]<>"Open" && DATEDIFF(QNCIncidents[Closed Date],min('Dates'[Date]),MONTH)>=36))

one part is open and more than 3 years , the other part is closed, However, the close date is three years later than the date in datetable.

[Anonymous]

Thanks for the reply. However, including 'status' in the measure calculation would give me a wrong count. This is because,

Let's consider Jan 2022 period to see open incidents older than Jan 2019.

In this case, an incident 'A' older than Jan 2019 which was open in Jan 2019 and got closed on July 2022 will show as 'closed' status in my current data extraction snapshot.

So, going by status filter would exclude this incident.