Building a visual to track Ticket status and Cost

[Ray_Brosius]

I've asked this question before but have not found a suitable solution, so giving the community forum another crack at this. 

At the bottom of this post, I have provided a very simple example table which contains the "data".    The data is a very basic snapshot table which contains a row for each ticket for each day.

I have  provided a screenshot of  the "example" output I am looking for.

Basically in a report a user would dynamically select the Date Range to evaluate..  This typically would be done by providing a slicer on the snapshot date column.  in the screenshot Showing the example with Start = 1/5/2022 and End = 1/9/2022.  They could also filter the report based on the Category or any other Attribute that might be in the data. ( not showing other attributes to keep it simple).

We want to show what the Tickets looked like on the first day of the evaluation timeframe the number of tickets per status and the Value $$'s...  then for each Ticket Status show where the tickets ended up. 

• For the 2 tickets that were in "New" with a value of $4 they ended up with 1  "In Progress" with a value of $1  and 1 "Closed" with a value of  $10.  
• For the 1 ticket that was "In Progress" at $1  it ended up in "In Progress" with a value of $3

Ticket ID	Status	Category	Value	Snapshot Date
1	New	Hardware	$1	1/1/2022
2	New	Question	$3	1/1/2022
3	New	Hardware	$1	1/1/2022
1	New	Hardware	$1	1/2/2022
2	New	Question	$3	1/2/2022
3	New	Hardware	$1	1/2/2022
1	New	Hardware	$1	1/3/2022
2	New	Question	$3	1/3/2022
3	New	Hardware	$1	1/3/2022
1	New	Hardware	$1	1/4/2022
2	New	Question	$3	1/4/2022
3	New	Hardware	$1	1/4/2022
1	New	Hardware	$1	1/5/2022
2	New	Question	$3	1/5/2022
3	In Progess	Hardware	$1	1/5/2022
1	New	Hardware	$1	1/6/2022
2	InProgress	Question	$9	1/6/2022
3	In Progess	Hardware	$1	1/6/2022
1	InProgress	Hardware	$2	1/7/2022
2	Closed	Question	$10	1/7/2022
3	In Progess	Hardware	$1	1/7/2022
1	InProgress	Hardware	$2	1/8/2022
2	Closed	Question	$10	1/8/2022
3	In Progess	Hardware	$1	1/8/2022
1	InProgress	Hardware	$3	1/9/2022
2	Closed	Question	$10	1/9/2022
3	In Progess	Hardware	$1	1/9/2022
1	InProgress	Hardware	$3	1/10/2022
2	Closed	Question	$10	1/10/2022
3	Closed	Hardware	$1	1/10/2022
1	InProgress	Software	$6	1/11/2022
2	Closed	Question	$10	1/11/2022
3	Closed	Hardware	$1	1/11/2022
1	Testing	Software	$10	1/12/2022
2	Closed	Question	$10	1/12/2022
3	Closed	Hardware	$1	1/12/2022
1	Testing	Software	$10	1/13/2022
2	Closed	Question	$10	1/13/2022
3	Closed	Hardware	$1	1/13/2022
1	Closed	Software	$20	1/14/2022
2	Closed	Question	$10	1/14/2022
3	Closed	Hardware	$1	1/14/2022

[Ray_Brosius]

I wonder if I Phrase the issue differently, will it help to articluate the challenge I am trying to solve.

1) We have data in a "snapshot" type model  1 row per ticket per day showing the values for each ticket on each day

2) We want to create a report where the user can select two snapshot dates to Evaluate. Start Date and End Date

3) We want to show the movement of tickets between the Start Date and the End Date for each Ticket Status.

It is "easy" to count the number of tickets in each status at the start.. and then the number of tickets in each status at the end.  But we want to show for each Status at the start where the tickets, in that status, ended up at the end.

This requires a dynamic filtering of the Fact table by the Start Date and Status then Joining that list of Ticket IDs to a dynamically filterd version of the Fact table by the End Date and then counting the Tickets in the end status.. 

ON the surface this does not seem like a UNIQUE or CUSTOM solution to develop.  We are basically trying to track the "FLOW" of Tickets through a defined workflow.   

[Ray_Brosius · Accepted solution]

I was able to figure out how to calculate the Flow of Tickets based on user selected evaluation dates.. 

I pasted some of the code below an an updated "Visual"  The Visual shows the Ticket count per status at the start and at the end..    Then the "Started In" table shows  "where" the tickets at the end "started in"  So the  In Progress ticket row shows that there were 2 at the end and one came from the new status and one came from the In Progress Status.

Created a measure to count the number of Tickets at the Start and At the end filtering by the respective "Evaluation Dates".  #Tickets at Start and #Tickets at End

#Tickets at Start =

VAR _EvalStartDate = SELECTEDVALUE( 'Snapshot Start'[Start Date] )

VAR _Tickets =

CALCULATE(

COUNT( 'Tickets'[Ticket ID] ),

FILTER('Tickets','Tickets'[Snapshot Date] = _EvalStartDate)
)

RETURN

if(_Tickets=BLANK(),0,_Tickets)

#Tickets at End =

VAR _EvalEndDate = SELECTEDVALUE( 'Snapshot End'[End Date])

VAR _Tickets =

CALCULATE(

COUNT( 'Tickets'[Ticket ID] ),

FILTER('Tickets','Tickets'[Snapshot Date] = _EvalEndDate)
)

RETURN

if(_Tickets=BLANK(),0,_Tickets)

At_Start_New_Status is a measure that determines if a record was in a specific status at the start. . Showing in the below only the measure for the status of NEW .  Each status would need it's own measure.

At_Start_New_Status =

VAR _Ticket =SELECTEDVALUE( 'Tickets'[Ticket ID] )

VAR _EvalStartDate = SELECTEDVALUE( 'Snapshot Start'[Start Date] )

VAR Result =

IF( COUNTROWS(

FILTER(

FILTER( 

ALL( 'Tickets' ),

_Ticket = 'Tickets'[Ticket ID] && 'Ticket'[Status]="New"

   ),

_EvalStartDate = 'Tickets'[Snapshot Date]

)

    )
> 0,
1,
0
)

RETURN _Result

#Tickets_at_End_Started_in_New is a measure that counts the number of records at the end which were in a the status of "New" at the start evaluation date.

#Tickets_at_End_Started_in_New =

VAR _Ticket = SELECTEDVALUE( 'Tickets'[Ticket ID] )
VAR _EvalEndDate = SELECTEDVALUE( 'Snapshot End'[End Date] )

VAR _Result =

CALCULATE(

COUNTROWS(

FILTER( 'Tickets',

'Tickets'[Snapshot Date] = _EvalEndDate &&
[At_Start_New_Status] = 1

    )

)

)

RETURN _Result

#New Tickets is a measure to count the tickets that were created after the Start Date.

#New Tickets=

VAR _TicketID = SELECTEDVALUE( 'Tickets'[Ticket ID] )
var EvalEndDate = SELECTEDVALUE( 'Snapshot End'[End Date] )
VAR _New =

CALCULATE(

COUNTROWS(
FILTER( 'Tickets',

'Tickets'[Snapshot Date] = EvalEndDate &&
[New Deal] = 1
)
)
)

RETURN  _New 

New Ticket  is a measure to determine if a Ticket was created AFTER the Start Date.  To do this created one measure to determine if the ticket was in the start date and one to determine if the ticket was in the end date.  (Record in Start Eval Period, Record in End Eval Period )

New Ticket =

var SelectedTicket = SELECTEDVALUE('Tickets'[Ticket ID])
var Result = IF( [Record in Start Eval Period] =0 && [Record in End Eval Period] =1,1,0)
Return Result

Record in Start Eval Period =

VAR SelectedTicket = SELECTEDVALUE('Tickets'[Ticket ID])
var EvalStartDate = SELECTEDVALUE( 'Snapshot Start'[Start Date] )
var Result =

IF(
COUNTROWS(

FILTER(

FILTER(ALL('Tickets'),

SelectedTicket = 'Tickets'[Ticket ID]
),

EvalStartDate = 'Tickest'[Snapshot Date]

)

)>0,1,0

)

Return Result

Record in End Eval Period =

VAR SelectedTicket = SELECTEDVALUE('Tickets'[Ticket ID])
var EvalEndDate = SELECTEDVALUE( 'Snapshot End'[End Date] )
var Result =

IF(

COUNTROWS(

FILTER(

FILTER(ALL('Tickets'),

SelectedTicket = 'Tickets'[Ticket ID]

),

EvalEndDate = 'Tickest'[Snapshot Date]
)

)>0,1,0

)

Return Result

[DataInsights]

@Ray_Brosius,

Try this solution.

1. Create separate tables for Start Date and End Date (no relationships):

2. Create measures:

Starting Status Count = 
CALCULATE (
    COUNT ( FactTable[Ticket ID] ),
    FactTable[Snapshot Date] = SELECTEDVALUE ( StartDate[Date] )
)

Starting Status Value = 
CALCULATE (
    SUM ( FactTable[Value] ),
    FactTable[Snapshot Date] = SELECTEDVALUE ( StartDate[Date] )
)

Ending Status Count = 
CALCULATE (
    COUNT ( FactTable[Ticket ID] ),
    FactTable[Snapshot Date] = SELECTEDVALUE ( EndDate[Date] )
)

Ending Status Value = 
CALCULATE (
    SUM ( FactTable[Value] ),
    FactTable[Snapshot Date] = SELECTEDVALUE ( EndDate[Date] )
)

3. Create slicers for Start Date (StartDate table) and End Date (EndDate table).

4. Result:

[Ray_Brosius]

Thanks for your reply but you did not provide a solution to the "challenging" aspsect of this. 

We want to show the Ticket "FLOW"..   

the number of tickets per status and the Value $$'s...At the START

  then for each Ticket Status show where the tickets ended up.  (See screenshot provided for expected output) The "FLOW"  # tickets at the start for each Status then show the Number of tickets by status that those tickets ended up in at the MAX of the Evaluation Date.. 

• Example ( See Screenshot for expected output.)
• For the 2 tickets that were in "New" with a value of $4 they ended up with 1  "In Progress" with a value of $1  and 1 "Closed" with a value of  $10.  
• For the 1 ticket that was "In Progress" at $1  it ended up in "In Progress" with a value of $3

What is needed is a way to get the "Listing" of deals by status, at the start of the evaluation date, then use that "temporary" table to show how many by each status at the start,  and the ending Status Count

So if I had 100 Tickets in New at the Start.. I would find 100 tickets at the end.. but those 100 woudl be distributed across different status values.. that is the 100 tickets at the start could be in any one of the status's at the end.. 

[DataInsights]

@Ray_Brosius,

See if this works. I created a separate table EndingStatus to use in the Ending Status matrix. This allows you to control the filtering via DAX. This table can be created in Power Query or DAX, and has no relationships.

Calculated table:

EndingStatus = DISTINCT ( FactTable[Status] )

Modify the measures as follows:

Ending Status Count = 
VAR vStartTickets =
    CALCULATETABLE (
        VALUES ( FactTable[Ticket ID] ),
        FactTable[Snapshot Date] = SELECTEDVALUE ( StartDate[Date] )
    )
VAR vResult =
    CALCULATE (
        COUNT ( FactTable[Ticket ID] ),
        FactTable[Snapshot Date] = SELECTEDVALUE ( EndDate[Date] ),
        vStartTickets,
        TREATAS ( VALUES ( EndingStatus[Status] ), FactTable[Status] )
    )
RETURN
    vResult

Ending Status Value = 
VAR vStartTickets =
    CALCULATETABLE (
        VALUES ( FactTable[Ticket ID] ),
        FactTable[Snapshot Date] = SELECTEDVALUE ( StartDate[Date] )
    )
VAR vResult =
    CALCULATE (
        SUM ( FactTable[Value] ),
        FactTable[Snapshot Date] = SELECTEDVALUE ( EndDate[Date] ),
        vStartTickets,
        TREATAS ( VALUES ( EndingStatus[Status] ), FactTable[Status] )
    )
RETURN
    vResult

In the Ending Status matrix, use EndingStatus[Status] as columns:

[Ray_Brosius]

Very much appreciate your time and thoughts on this..  but this is not quite what we are after..  I simplified the expected output.

For each Status..

We want how many tickets were in each status at the start.

Then on the same line show for the tickets that were in each status, how many went to each possible status at the end.

[DataInsights]

@Ray_Brosius,

This sounds like a visual issue (showing them on the same line). You could try customizing the native matrix to get it to display properly. Alternatively, you could try the Profitbase matrix or Inforiver matrix (they offer more features).