Forum Discussion
Average Backlog Age by Month
In a prior post, I was helped considerably in grouping a list of cases in "Age Bands" by month for the month in which the case was Open (i.e. not closed). The graph worked well and I got something looking like this:
Now however I need to add a line to the above chart that shows the Average Age of ALL cases that were Open in that month, rather than just counting how many had been open for 30 days, 60 days, etc.
I used a modified version of one of the "time band" formulas to come up with the total cases that were Open in a given month:
Total Active = CALCULATE(COUNT(Backlog[CaseNumber]), FILTER(ALLSELECTED(Backlog),
Backlog[CreatedDate] < 'Month'[MonthEnd] && Backlog[Adj_ClosedDate] > 'Month'[MonthEnd]))
That seems to get me what I need for a total count of cases, but how do I figure out an Average Age for all the cases included in that count for that month?
5 Replies
Simplify your model and share us a complete example. You may first take a look at DATEDIFF Function.
Here is some sample data. It's basically CaseID, DateOpened and DateClosed for each case:
CaseID DateOpened DateClosed 1007 12/28/2016 1/15/2017 1008 12/29/2016 2/15/2017 1009 1/1/2017 1/15/2017 1010 1/1/2017 2/15/2017 1011 1/15/2017 2/10/2017 1012 1/26/2017 1/28/2017 1013 1/31/2017 1/31/2017 1014 2/5/2017 2/9/2017 1015 2/25/2017 2/28/2017 1016 2/25/2017 3/1/2017 1017 2/28/2017 3/1/2017 1018 2/28/2017 5/26/2017 1019 3/1/2017 3/27/2017 1020 3/5/2017 3/21/2017 1021 3/26/2017 4/2/2017 1022 3/28/2017 1023 4/15/2017 4/16/2017 1024 4/19/2017 6/19/2017 1025 4/30/2017 7/30/2017 1026 5/25/2017 6/2/2017 1027 6/2/2017 1028 6/10/2017 6/21/2017 1029 6/25/2017 1030 7/14/2017 16-Oct 1031 8/21/2017 8/22/2017 1032 8/23/2017 8/25/2017 1033 9/1/2017 9/1/2017 1034 9/3/2017 9/30/2017 1035 9/5/2017 10/12/2017 1036 9/5/2017 10/5/2017 1037 9/10/2017 11/21/2017 1038 9/12/2017 1039 9/26/2017 12/1/2017 1040 9/30/2017 11/15/2017 1041 9/30/2017 10/1/2017 1042 10/11/2017 10/31/2017 1043 10/12/2017 11/3/2017 1044 10/15/2017 1045 10/24/2017 1046 10/26/2017 10/27/2017 1047 10/29/2017 1048 10/30/2017 1049 10/31/2017 11/15/2017 1050 10/31/2017 When I look at this data in Excel, I can easily create a formula that will check to see the following for each case for the END of a given month:
- Was the Case opened before the end date for that month?
- Is the Case still Open? If so, subtract the DateOpened from the End of Month date.
- If there is a DateClosed, is that date later than the End date for that Month? If so, subtract the DateOpened from the End of Month date.
Using this data, I can create the following matrix:
Age of any Case Still Open at Months End CaseID DateOpened DateClosed 1/31/2017 2/28/2017 3/31/2017 4/30/2017 5/31/2017 6/30/2017 7/31/2017 8/31/2017 9/30/2017 10/31/2017 11/30/2017 12/31/2017 1007 12/28/2016 1/15/2017 - - - - - - - - - - - - 1008 12/29/2016 2/15/2017 33 - - - - - - - - - - - 1009 1/1/2017 1/15/2017 - - - - - - - - - - - - 1010 1/1/2017 2/15/2017 30 - - - - - - - - - - - 1011 1/15/2017 2/10/2017 16 - - - - - - - - - - - 1012 1/26/2017 1/28/2017 - - - - - - - - - - - - 1013 1/31/2017 1/31/2017 - - - - - - - - - - - - 1014 2/5/2017 2/9/2017 - - - - - - - - - - - - 1015 2/25/2017 2/28/2017 - - - - - - - - - - - - 1016 2/25/2017 3/1/2017 - 3 - - - - - - - - - - 1017 2/28/2017 3/1/2017 - 0 - - - - - - - - - - 1018 2/28/2017 5/26/2017 - 0 31 61 - - - - - - - - 1019 3/1/2017 3/27/2017 - - - - - - - - - - - - 1020 3/5/2017 3/21/2017 - - - - - - - - - - - - 1021 3/26/2017 4/2/2017 - - 5 - - - - - - - - - 1022 3/28/2017 - - 3 33 64 94 125 156 186 217 247 278 1023 4/15/2017 4/16/2017 - - - - - - - - - - - - 1024 4/19/2017 6/19/2017 - - - 11 42 - - - - - - - 1025 4/30/2017 7/30/2017 - - - 0 31 61 - - - - - - 1026 5/25/2017 6/2/2017 - - - - 6 - - - - - - - 1027 6/2/2017 - - - - - 28 59 90 120 151 181 212 1028 6/10/2017 6/21/2017 - - - - - - - - - - - - 1029 6/25/2017 - - - - - 5 36 67 97 128 158 189 1030 7/14/2017 16-Oct - - - - - - 17 48 78 - - - 1031 8/21/2017 8/22/2017 - - - - - - - - - - - - 1032 8/23/2017 8/25/2017 - - - - - - - - - - - - 1033 9/1/2017 9/1/2017 - - - - - - - - - - - - 1034 9/3/2017 9/30/2017 - - - - - - - - - - - - 1035 9/5/2017 10/12/2017 - - - - - - - - 25 - - - 1036 9/5/2017 10/5/2017 - - - - - - - - 25 - - - 1037 9/10/2017 11/21/2017 - - - - - - - - 20 51 - - 1038 9/12/2017 - - - - - - - - 18 49 79 110 1039 9/26/2017 12/1/2017 - - - - - - - - 4 35 65 - 1040 9/30/2017 11/15/2017 - - - - - - - - 0 31 - - 1041 9/30/2017 10/1/2017 - - - - - - - - 0 - - - 1042 10/11/2017 10/31/2017 - - - - - - - - - - - - 1043 10/12/2017 11/3/2017 - - - - - - - - - 19 - - 1044 10/15/2017 - - - - - - - - - 16 46 77 1045 10/24/2017 - - - - - - - - - 7 37 68 1046 10/26/2017 10/27/2017 - - - - - - - - - - - - 1047 10/29/2017 - - - - - - - - - 2 32 63 1048 10/30/2017 - - - - - - - - - 1 31 62 1049 10/31/2017 11/15/2017 - - - - - - - - - 0 - - 1050 10/31/2017 - - - - - - - - - 0 30 61 I can then do a simple Count formula to see how many cases were open at the end of a given month (they may be closed in a future month, but at the end of that month they were still open):
Cases Open at Months End Jan Feb Mar Apr May Jun Jul Aug Sep Oct 3 3 3 4 4 4 4 4 11 14 And, I can do simple Average formula to see the Average Age of the Cases that were open at the end of a given month:
Average Age of Cases Open at Months End Jan Feb Mar Apr May Jun Jul Aug Sep Oct 26.33 1.00 13.00 26.25 35.75 47.00 59.25 90.25 52.09 50.50 So, in the Matrix above, it is easy to see that there were 3 cases still open at the end of January, and they had been open for an average of 26.33 days.
The thing I cannot seem to be able to do is duplicate this in Power BI.
