cancel
Showing results for
Did you mean:
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 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.

1 ACCEPTED SOLUTION

Accepted Solutions
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!

5 REPLIES 5
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.

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")),

YearNumber = Table.AddColumn(DateToNumber , "Year", each Date.Year([Date])),

QuarterNumberFormated = Table.AddColumn(YearNumber , "Quarter Formated", each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
QuarterNumber = Table.AddColumn(QuarterNumberFormated , "Quarter", each  Date.QuarterOfYear([Date])),

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),

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.

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!

Frequent Visitor

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

Here you go. Also original file is updated.

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!!!!

Announcements

#### 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?

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

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