Forum Discussion
Average Backlog Age by Month
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.
I guess my main issue is how I would integrate that into a DAX (or other) formula. I know how to get the total per month, I just don't know how to calculate the Age of that total.
Ryan, were you ever able to solve these challenges? I am struggling with something similar, and am brand new to Power BI
