Forum Discussion
Anonymous
6 years agoNot 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, Sta...
- Anonymous6 years ago
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
6 years agoNot 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.
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
SourceI did all this in this file here.
Anonymous
5 years agoNot applicable
I am not able to download the file. Please help