Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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!
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.
I am not able to download the file. Please help
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!
Here you go. Also original file is updated.
If this resolves your question then please mark it as solution.
Please consider giving kudos if posts are helpful.
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!!!!