Forum Discussion
Solved
Cumulative Count of Rows between for a Given Date Range
Hello there, I track the number of defects that are reported by our customers and, amongst other things, I want to be able to track the cumultive number of tickets that have been reported over time....
Hi RB16kb,
You can create a expand table to expand date range from original table, then use expand table to create visual:
Table = VAR maxdate = MAXX ( Table2, [Resolved Date] ) VAR _Calendar = CALENDAR ( MIN ( Table2[Created Date] ), IF ( maxdate > TODAY (), maxdate, TODAY () ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table2, _Calendar ), Table2[Created Date] <= [Date] && IF ( Table2[Resolved Date] <> BLANK (), Table2[Resolved Date], TODAY () ) >= [Date] ), "Issue ID", [Issue ID], "Created Date", [Created Date], "Resolved Date", [Resolved Date], "Detail Date", [Date] )Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,Xiaoxin Sheng
The image below is a representation of what it is I am looking to acheive.
The more I think about it the more I suspect I need to create a custom table based on the data I am supplied. In otherwords the needs to be formated in to a table like that below:
| 2016 | 2017 | 2018 | |
| Jan | 44 | 20 | 21 |
| Feb | 80 | 40 | 38 |
| Mar | 105 | 68 | 65 |
| Apr | 132 | 87 | 103 |
| May | 166 | 107 | 126 |
| Jun | 196 | 127 | 139 |
| Jul | 227 | 136 | 152 |
| Aug | 245 | 149 | |
| Sep | 265 | 159 | |
| Oct | 284 | 172 | |
| Nov | 312 | 193 | |
| Dec | 333 | 204 |
Sample Data
| Issue ID | Created Date | Resolved Date |
| AAA-0758 | 03/01/2017 | 13/04/2017 |
| AAA-0759 | 04/01/2017 | 19/05/2017 |
| AAA-0778 | 01/02/2017 | 16/08/2017 |
| AAA-0779 | 06/02/2017 | 15/02/2017 |
| AAA-0780 | 06/02/2017 | 02/05/2017 |
| AAA-0798 | 01/03/2017 | 08/11/2017 |
| AAA-0799 | 02/03/2017 | 14/03/2017 |
| AAA-0800 | 06/03/2017 | 28/03/2017 |
| AAA-0826 | 04/04/2017 | 05/06/2017 |
| AAA-0827 | 04/04/2017 | 01/09/2017 |
| AAA-0828 | 04/04/2017 | 22/01/2018 |
| AAA-0845 | 02/05/2017 | 15/11/2017 |
| AAA-0846 | 03/05/2017 | 27/07/2017 |
| AAA-0847 | 03/05/2017 | 30/11/2017 |
| AAA-0848 | 08/05/2017 | 09/07/2018 |
| AAA-0849 | 15/05/2017 | 21/07/2017 |
| AAA-0865 | 05/06/2017 | 03/07/2017 |
| AAA-0866 | 09/06/2017 | 27/07/2017 |
| AAA-0867 | 12/06/2017 | 10/07/2017 |
| AAA-0868 | 13/06/2017 | 06/09/2017 |
| AAA-0869 | 13/06/2017 | 20/10/2017 |
| AAA-0870 | 14/06/2017 | 19/06/2017 |
| AAA-0871 | 14/06/2017 | 26/06/2017 |
| AAA-0885 | 07/07/2017 | 03/08/2017 |
| AAA-0886 | 07/07/2017 | 15/08/2017 |
| AAA-0894 | 01/08/2017 | 06/09/2017 |
| AAA-0895 | 01/08/2017 | 26/09/2017 |
| AAA-0896 | 04/08/2017 | 20/04/2018 |
| AAA-0897 | 07/08/2017 | 21/08/2017 |
| AAA-0898 | 08/08/2017 | 24/08/2017 |
| AAA-0899 | 08/08/2017 | 03/11/2017 |
| AAA-0907 | 11/09/2017 | 21/09/2017 |
| AAA-0917 | 01/10/2017 | 10/10/2017 |
| AAA-0918 | 04/10/2017 | 09/11/2017 |
| AAA-0919 | 06/10/2017 | 29/01/2018 |
| AAA-0920 | 06/10/2017 | 12/02/2018 |
| AAA-0921 | 09/10/2017 | 08/11/2017 |
| AAA-0922 | 09/10/2017 | |
| AAA-0923 | 10/10/2017 | 13/11/2017 |
| AAA-0930 | 01/11/2017 | |
| AAA-0931 | 02/11/2017 | 17/11/2017 |
| AAA-0932 | 03/11/2017 | 13/11/2017 |
| AAA-0951 | 05/12/2017 | 11/01/2018 |
| AAA-0952 | 05/12/2017 | 30/04/2018 |
| AAA-0953 | 06/12/2017 | 07/12/2017 |
| AAA-0954 | 07/12/2017 | 18/12/2017 |
| AAA-0962 | 04/01/2018 | 29/01/2018 |
| AAA-0963 | 09/01/2018 | |
| AAA-0964 | 09/01/2018 | |
| AAA-0965 | 10/01/2018 | 15/03/2018 |
| AAA-0966 | 11/01/2018 | 07/02/2018 |
| AAA-0967 | 11/01/2018 | 26/04/2018 |
| AAA-0968 | 12/01/2018 | |
| AAA-0983 | 01/02/2018 | 20/04/2018 |
| AAA-0984 | 01/02/2018 | |
| AAA-0985 | 04/02/2018 | 19/02/2018 |
| AAA-0986 | 05/02/2018 | 09/03/2018 |
| AAA-0987 | 06/02/2018 | |
| AAA-0988 | 07/02/2018 | 08/03/2018 |
| AAA-0989 | 07/02/2018 | |
| AAA-0990 | 09/02/2018 | 14/03/2018 |
| AAA-1000 | 05/03/2018 | 08/03/2018 |
| AAA-1001 | 07/03/2018 | 17/03/2018 |
| AAA-1002 | 08/03/2018 | 13/03/2018 |
| AAA-1003 | 08/03/2018 | 14/03/2018 |
| AAA-1004 | 08/03/2018 | 19/03/2018 |
| AAA-1005 | 08/03/2018 | 22/03/2018 |
| AAA-1006 | 08/03/2018 | 23/03/2018 |
| AAA-1007 | 08/03/2018 | |
| AAA-1008 | 08/03/2018 | |
| AAA-1009 | 09/03/2018 | |
| AAA-1010 | 12/03/2018 | 16/04/2018 |
| AAA-1011 | 13/03/2018 | 22/03/2018 |
| AAA-1012 | 13/03/2018 | 26/03/2018 |
| AAA-1013 | 13/03/2018 | 28/03/2018 |
| AAA-1027 | 03/04/2018 | |
| AAA-1028 | 04/04/2018 | 05/04/2018 |
Hi RB16kb,
You can create a expand table to expand date range from original table, then use expand table to create visual:
Table = VAR maxdate = MAXX ( Table2, [Resolved Date] ) VAR _Calendar = CALENDAR ( MIN ( Table2[Created Date] ), IF ( maxdate > TODAY (), maxdate, TODAY () ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( Table2, _Calendar ), Table2[Created Date] <= [Date] && IF ( Table2[Resolved Date] <> BLANK (), Table2[Resolved Date], TODAY () ) >= [Date] ), "Issue ID", [Issue ID], "Created Date", [Created Date], "Resolved Date", [Resolved Date], "Detail Date", [Date] )Spread revenue across period based on start and end date, slice and dase this using different dates
Regards,Xiaoxin Sheng