Calculating Average Case Age of Open Cases by Month

[Rmilczarek]

Hello,

I am trying to use Power BI to report on the Average Age of Cases that were "open" in a certain timeframe, by Month.  So basically, we have a dataset with the following info:

Incident	Status	Created On	Closed On
30313198	Closed	6/1/2017 0:02	6/25/2017 3:51
30313199	Closed	6/5/2017 0:04	6/6/2017 3:15
30313205	Referred - Internal	6/1/2017 0:20
30313207	Closed	4/4/2017 0:24	7/7/2017 3:16
30313208	Closed	4/1/2017 0:48	7/11/2017 3:01
30313211	Closed	7/1/2017 1:12	7/7/2017 4:19
30313215	Closed	7/3/2017 1:27	7/7/2017 5:20
30313217	Closed	5/1/2017 1:36	7/2/2017 2:10
30313219	Closed	7/1/2017 1:37	7/12/2017 6:55
30313220	Closed	6/1/2017 1:45	6/12/2017 1:51
30313222	Closed	6/1/2017 1:52	6/6/2017 2:26
30313223	Closed	4/2/2017 1:53	6/13/2017 6:09
30313224	Closed	4/3/2017 1:54	6/6/2017 7:27
30313225	Closed	6/1/2017 1:55	6/6/2017 2:43
30313226	Closed	6/1/2017 1:58	6/1/2017 3:08
30313227	Resolved	6/1/2017 1:58
30313230	Closed	5/1/2017 2:09	6/1/2017 2:14
30313233	Referred - Internal	5/2/2017 2:25
30313235	Waiting for Customer	6/2/2017 2:36
30313236	Closed	6/1/2017 2:38	6/6/2017 8:30
30313240	Closed	6/1/2017 2:42	6/15/2017 8:29
30313241	Referred - Internal	6/1/2017 2:42
30313242	Closed	6/1/2017 2:44	6/14/2017 8:50
30313249	Closed	6/1/2017 3:03	6/20/2017 23:32
30313250	Referred - Internal	6/1/2017 3:16

I am trying to calculate how many incidents were open at the end of a particular month (they may not be open now, but were open during the month I am reporting on) and what the Average Age was at that time for those cases.  A bonus would be how many were open for longer than 60 days at the end of the month in question.

I would like to report this, by month, for the past 15 months (the data above is of course just a subset with only 4 months represented).

I have tried several DAX calculations referenced in the forum, but just cannot seem to get something that does not error out or show bad results.

Any advice would be appreciated.  Thanks!

Ryan

[v-sihou-msft]

@Rmilczarek

In this scenario, since you need to analyze the data on month level, I suggest you create a YearMonth column based on "Create On" column. 

YearMonth =
LEFT ( FORMAT ( Table[Create On], "yyyyMMdd" ), 6 )

To calculate the count of "open" cases, you can create a measure like below:

Total Open Month =
CALCULATE (
    COUNTA ( Table[Incident] ),
    ALLEXCEPT ( Table, Table[YearMonth] ),
    FILTER ( Table, Table[Closed On] = BLANK () )
)

To calculate the Avg Case Age of Open Cases per Month, you need to add a variance column first. 

Variance =
IF (
    Table[Closed On] = BLANK (),
    1
        * ( EOMONTH ( Table[Create On], 0 ) - Table[Create On] ),
    BLANK ()
)

Then you can create a measure like below:

Avg Case Age of Open Cases per Month =
CALCULATE ( AVERAGE ( Table[Variance] ), ALLEXCEPT ( Table, Table[YearMonth] ) )

Regards,

[Rmilczarek]

I see where a lot of this would work, but again there is the issue of the case being closed for the timeframe that would be reported on.  Here is what I mean: 

Case #1 was Opened January 1st and Closed on May 23rd

Case #2 was Opened January 1st and Closed on June 15th

Case #3 was Opened on February 10th and is still open

If I were to report on how many cases were "open" at the end of the month from January to July, it would look like this

January - 2 Cases in Backlog (#1 and #2)

February - 3 Cases in Backlog (#1, #2 and #3)

March and April - 3 Cases in Backlog (#1, #2 and #3)

May - 2 Cases in Backlog (#2 and #3, because #1 closed before the end of May)

June and July - 1 Case in Backlog (#3 because #2 closed before the end of June)

So, I could not use any filtering that would include a "status = Open (or Not Closed)" because if I were to report on that today, the Closed dates are already filled in (since #1 and #2 closed in the past) but at the timeframe Month in the report, they were "open", so that report, if run today would look like this:

January - 0 Cases in Backlog (because #1 and #2 are closed as of today)

February to July - 1 case in Backlog (because #3 is the only one that has no Closed date)

Does that make sense?

Ryan

[AlbertoFerrari · Accepted solution]

Yep, it is a very common scenario, and a somewhat complex one.

I wrote an article (Analyzing events with a duration in DAX ) that analyzes quite deeply how to solve this specific scenario. If you go through the implementation of the full (fastest) model described there, performances are amazing, even if it will require some effort, both on the model and in DAX.

If, on the other hand, you only have a few million rows, then in the article you will also find the suboptimal versions, which still work fine for small datasets.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[CahabaData]

clarification: is the definition of 'open' = blank in Close On field? ...regardless of Status code ?

[Rmilczarek]

Thanks for your question.  Unfortunately it would not be that straightforward as I am wanting an historic trend of cases that were "open" in the past (so the date I would measure would include cases that may have already been closed at some future date, so the Closed on field would be populated.

In this case, "open" = "not closed" at the time of the Monthly measure.  In other words, if I am measuring Open Cases by Month from January to August, then for January, I would want a count of how many cases were opened before January 31st and were not closed until after that day.  For Feburary, how many cases were opened before February 28 and not closed until after that day.  

The trick however would be finding a way to calculate the "age" of those cases for their respective timeframes.

I hope that makes sense as I confuse myself everytime I say it!