Count closed incidents which were completed in more than 30 days; count historical numbers

[j3420]

Hi all,

I have a question. I was wondering if someone might be able to help me out with it. 

I have a task to count the number of incidents which were still open and past 30 days from the date of an occurrence?

In a database when a close date is populated, I can't find out a way to count how many incidents were open on any particular day, since completion date is already populated!

I couldn't attach excel file to it! if someone knows how to do it please let me know!

The output should be, for example, if I'm reporting if, on 4/21/2018, I should get a count of all the incident which were completed after 30 days and investigated completion date is less than 4/21/2018.

Input dataResults

Thanks in advance

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Investigation completed date column to the Date column of the Calendar Table.  Write this measure

=CALCULATE(COUNTROWS(Date),FILTER(Data,Data[Investigation Completed]<=MAX(Calendar[Date])&&Data[Days Taken]>30))

Hope this helps.

[j3420]

Thanks for your reply!

However, the solution I'm looking for would be quite different. 

Please see a screenshot! on any particular week, for example, let's just say at the end of last week 4/13/2019, I would like to know all the open investigation that are past 30 days as a whole number (basically need to get rid of the filter, could be used selected value function or something) and I'd also like to know historical numbers as well for previous weeks that what would've been open on that week. However, once an Investigation date is populated it's not open anymore but would like to know what would've been open on that particular date.

In simple words, let's just say on 1/12/2019, I would like to know how many incidents were open that were past due 30 days. it's not that easy now because the investigation date has already been populated for those incidents. So, there need to some logic behind it that use that date 1/12/2019 as cut off and see what is open and past 30 days on that day and if looking at the historical numbers now, what incidents were closed that took more than 30 days.

I don't think if I have explained it right. But I really appreciate your time on helping me out.

Thanks,

[Ashish_Mathur]

Thank you for clarifying.  I have read your question twice and am just not able to understand.