Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 DateEnd DateStatus
1001/1/20191/12/2019Open
1001/13/20192/15/2019In Progress
1002/16/20192/20/2019Blocked
1002/21/20193/10/2019In Progress
1003/10/20193/10/2019Done
1021/4/201910/16/2019Open
1101/15/20195/13/2019Open
1105/14/20196/1/2019In Progress
1106/1/20196/1/2019Done
1202/20/201910/16/2019Open

 

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.

  • Anonymous's avatar
    Anonymous
    6 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

  • Anonymous's avatar
    Anonymous
    Not 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
        Source

    I did all this in this file here.

    • Anonymous's avatar
      Anonymous
      Not 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!

      • Anonymous's avatar
        Anonymous
        Not applicable

        Here you go. Also original file is updated.

        If this resolves your question then please mark it as solution.

         

    • Anonymous's avatar
      Anonymous
      Not applicable

      I am not able to download the file. Please help