Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

View solution in original post

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


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.

I am not able to download the file. Please help

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
Not applicable

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

Anonymous
Not applicable

Please consider giving kudos if posts are helpful.

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors