cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)

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!

View solution in original post

5 REPLIES 5
Highlighted
Helper I
Helper I

Re: Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)

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.


PBIDesktop_2019-10-17_11-11-18.png

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.

Highlighted
Frequent Visitor

Re: Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)

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!

View solution in original post

Highlighted
Helper I
Helper I

Re: Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)

Here you go. Also original file is updated.

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

 

PBIDesktop_2019-10-17_12-04-55.png

Highlighted
Helper I
Helper I

Re: Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)

Please consider giving kudos if posts are helpful.

Highlighted
Frequent Visitor

Re: Ticket Status Stack Chart Across time (Quarter, Monthly, Weekly)

Sang, This is an awesome response to my questions. The first response was right on and the 2nd response gave me great insights for this report and how to go forward with other reports. You responses were quick & to the point. Thanks!!!!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021