Forum Discussion
Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)
I have a set of Tickets that are in various stages of Status. A ticket's status may extend across several days. The data is from a status change log in the form of Ticket #, Start Date, End Date, Status.
Examples:
| Ticket# | Start Date | End Date | Status |
| 100 | 1/1/2019 | 1/12/2019 | Open |
| 100 | 1/13/2019 | 2/15/2019 | In Progress |
| 100 | 2/16/2019 | 2/20/2019 | Blocked |
| 100 | 2/21/2019 | 3/10/2019 | In Progress |
| 100 | 3/10/2019 | 3/10/2019 | Done |
| 102 | 1/4/2019 | 10/16/2019 | Open |
| 110 | 1/15/2019 | 5/13/2019 | Open |
| 110 | 5/14/2019 | 6/1/2019 | In Progress |
| 110 | 6/1/2019 | 6/1/2019 | Done |
| 120 | 2/20/2019 | 10/16/2019 | Open |
If a ticket extends into another date interval it needs to be counted in that interval. The column will total each status type (Open, In Progress, Blocked, Done) by color.
With a week interval:
Week 1 (Dec 31 - Jan 6) stack would have a count of 2 Open tickets (#100, #102) (Color blue). Height of 2.
Week 2 (Jan 7 - Jan 13) stack would have a count of 2 Open tickets (#100, #102) (blue), 1 In Progress ticket (#100)(Green). Height of 3.
I don't know how to read a row and then add the count to an interval stack & status buckets. Any ideas to get me going. I'd appreciate it.
Extra question: How do I change the interval from Week, Month, Quarter. Do I need 3 seperate Visuals?
Thanks!!!
etc.
- Anonymous6 years ago
This is great!! Could you enlighten me on how i could get it into a stack chart with colors representing different statuses (y-axis) with the time intervals(x-axis)? I'm a newbie and this would catapult me up. Thanks!
6 Replies
- AnonymousNot applicable
Use following to get the Dates between these Start and End Dates and then connect the table to a Date Table.
List.Dates([Start Date], Number.From([End Date]-[Start Date])+1, #duration(1,0,0,0))
Also create a Status table with all values of Status. You can use DAX table for that as shown below
MD_Status = VALUES(Data[Status])
Once you have all three tables connect them as shown.
At the end write Dax Measure Count:
Count = DISTINCTCOUNT(Data[Ticket#])
Here is the function I used for Date Table:
//Create Date Dimension let Source = (StartDate as date, EndDate as date)=> let //Capture the date range from the parameters StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), Date.Day(StartDate)), EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), Date.Day(EndDate)), //Get the number of dates that will be required for the table GetDateCount = Duration.Days(EndDate - StartDate), //Take the count of dates and turn it into a list of dates GetDateList = List.Dates(StartDate, GetDateCount, #duration(1,0,0,0)), //Convert the list into a table DateListToTable = Table.FromList(GetDateList, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), //Create various date attributes from the date column // Date Number DateToNumber = Table.AddColumn(DateListToTable, "DateAsInteger", each Date.ToText([Date], "YYYYMMDD")), //Add Year Column YearNumber = Table.AddColumn(DateToNumber , "Year", each Date.Year([Date])), //Add Quarter Column QuarterNumberFormated = Table.AddColumn(YearNumber , "Quarter Formated", each "Q" & Number.ToText(Date.QuarterOfYear([Date]))), QuarterNumber = Table.AddColumn(QuarterNumberFormated , "Quarter", each Date.QuarterOfYear([Date])), //Add Week Number Column WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", each Date.WeekOfYear([Date], 1)), // Start of week: 0 = Sunday, 1 = Monday, 2 = Tues, etc CurrentYear = Table.AddColumn(WeekNumber , "Day Of Week Name", each Date.DayOfWeekName([Date])), DayofWeekNumber = Table.AddColumn(CurrentYear , "Day of Week", each Date.DayOfWeek([Date],Day.Sunday) +1), //Add Month Number Column MonthNumber = Table.AddColumn(DayofWeekNumber, "Month Number", each Date.Month([Date])), MonthName = Table.AddColumn(MonthNumber , "Month Name", each Date.MonthName([Date])), MonthShortName = Table.AddColumn(MonthName , "Month Name Short", each Date.ToText([Date],"MMM")), DayOfMonth = Table.AddColumn(MonthShortName , "Day of Month", each Date.Day([Date])), // Is Functions IsInCurrentDay = Table.AddColumn(DayOfMonth , "IsInCurrentDay", each Date.IsInCurrentDay([Date])), IsInCurrentWeek = Table.AddColumn(IsInCurrentDay , "IsCurrentWeek", each Date.IsInCurrentWeek([Date])), IsInCurrentMonth = Table.AddColumn(IsInCurrentWeek , "IsCurrentMonth", each Date.IsInCurrentMonth([Date])), IsInCurrentQuarter = Table.AddColumn(IsInCurrentMonth , "IsCurrentQtr", each Date.IsInCurrentQuarter([Date])), IsInCurrentYear = Table.AddColumn(IsInCurrentQuarter , "IsCurrentYear", each Date.IsInCurrentYear([Date])), EndOfMonth = Table.AddColumn(IsInCurrentYear , "EndOfMonth", each Date.EndOfMonth([Date])), EndOfQuarter = Table.AddColumn(EndOfMonth , "EndOfQuarter", each Date.EndOfQuarter([Date])), EndOfWeek = Table.AddColumn(EndOfQuarter , "EndOfWeek", each Date.EndOfWeek([Date], Day.Saturday)), EndOfYear = Table.AddColumn(EndOfWeek , "EndOfYear", each Date.EndOfYear([Date])), IsInNextMonth = Table.AddColumn(EndOfYear , "IsNextMonth", each Date.IsInNextMonth([Date])), IsInPreviousDay = Table.AddColumn(IsInNextMonth , "IsPreviousDay", each Date.IsInPreviousDay([Date])), IsInPreviousMonth = Table.AddColumn(IsInPreviousDay , "IsPreviousMonth", each Date.IsInPreviousMonth([Date])), IsInPreviousQuarter = Table.AddColumn(IsInPreviousMonth , "IsPreviousQtr", each Date.IsInPreviousQuarter([Date])), IsInPreviousWeek = Table.AddColumn(IsInPreviousQuarter , "IsPreviousWeek", each Date.IsInPreviousWeek([Date])), IsInPreviousYear = Table.AddColumn(IsInPreviousWeek , "IsPreviousYear", each Date.IsInPreviousYear([Date])), IsInYearToDate = Table.AddColumn(IsInPreviousYear , "IsInYearToDate", each Date.IsInYearToDate([Date])), IsLeapYear = Table.AddColumn(IsInYearToDate , "IsLeapYear", each Date.IsLeapYear([Date])) in IsLeapYear in SourceI did all this in this file here.
- AnonymousNot applicable
This is great!! Could you enlighten me on how i could get it into a stack chart with colors representing different statuses (y-axis) with the time intervals(x-axis)? I'm a newbie and this would catapult me up. Thanks!
- AnonymousNot applicable
Here you go. Also original file is updated.
If this resolves your question then please mark it as solution.
- AnonymousNot applicable
I am not able to download the file. Please help