cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
steven_sparks Frequent Visitor
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
steven_sparks Frequent Visitor
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
Sang Frequent Visitor
Frequent Visitor

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.

steven_sparks Frequent Visitor
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

Sang Frequent Visitor
Frequent Visitor

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

Sang Frequent Visitor
Frequent Visitor

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

Please consider giving kudos if posts are helpful.

steven_sparks Frequent Visitor
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
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors